Converting from Recorded Macro - Deleting Found rows

[bokenrosie]

Hi All,

Seems pretty like a pretty simple macro recording task but I cannot seem to get to where I want to be.

I have a report that comes in by CSV monthly. I have started a recording macro but cannot get the delete range part right.

After I sort my sheet I need to search for a value in a column. When I get down to the row in the search I need to delete all rows from the row to the top (except the top row).

The number of rows will change monthly.

Would somebody help me with the costruction of this in VBA, I assume recording is not going to work because that takes

I think

Range("Your Range”).Delete Shift:=xlUp

does the deleting but how do I get my range in there?

TIA

 

[Fluff]

What value are you trying to find & which column is it in?

 

[bokenrosie]

What value are you trying to find & which column is it in?

Thanks for looking...

I'm searching for a string in one of the columns. Once I find it I hit home key to get to beginning of line, then shift-ctrl-home to take to top, then hold shift and go down arrow once.

this results in the range I want to delete - shift cells up.

 

[Fluff]

Can you please answer my questions?

 

[bokenrosie]

Can you please answer my questions?

Sorry, specifically I'm looking in column h for the first appearance of "USER-LAN"

 

[Fluff]

Ok, how about

Sub bokenrosie()
   Dim Fnd As Range
   
   Set Fnd = Range("H:H").Find("User-lan", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Range("A2:A" & Fnd.Row).EntireRow.Delete
   End If
End Sub

 

[bokenrosie]

Exactly what I needed, thanks.

 

[Fluff]

You're welcome & thanks for the feedback.