Help with Macro

  • Thread starter Thread starter Hardhorn
  • Start date Start date
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
 
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)
 
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!
 
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)
 
Back
Top