Help with Macro

H

Hardhorn

I need to create a macro to modify a spreadsheet into which I have
imported an address list. The steps I am unsure about are:-
Go to the last 2 rows which contain text and delete, the last row
could vary each time the list is imported.
In col A if an X is present delete the row.
In col H delete all rows which do not contain a V
Hope you can help
Thanks
Alistair
 
B

Bob Phillips

Alistair,

This should do it for you

Dim cLastRow As Long
Dim i As Long
Dim oRng As Range

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Set oRng = Cells(cLastRow, 1).EntireRow
Set oRng = Union(oRng, Cells(cLastRow - 1, 1).EntireRow)

For i = cLastRow - 2 To 1 Step -1
If Cells(i, "A").Value = "X" Or Cells(i, "H").Value <> "V" Then
Set oRng = Union(oRng, Cells(i, 1).EntireRow)
End If
Next i

oRng.Rows.Delete


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

Hardhorn

Hi Bob
Thanks for the reply. I am very much an Excel Macro virgin so forgive
me if my questions seem a bit dim.

I pasted your code into a macro and ran it, unfortunatly it deleted
the contents of the sheet! Did I do something wrong?

Would it help if I sent you a sample of the spreadsheet?

My regards
Alistair
......I am looking out of the window at a very bare Elderberry tree and
grey sky!
 
B

Bob Phillips

Alistair,

Feel free to post me the spreadsheet, but not my email address. It should be
bob dot phillips at tsicali dot co dot uk - transcribe the dot and at to .
and @ and remove the spaces.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top