Converting from Recorded Macro - Deleting Found rows

bokenrosie

New Member
Joined
Jun 13, 2014
Messages
48
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
What value are you trying to find & which column is it in?
 

bokenrosie

New Member
Joined
Jun 13, 2014
Messages
48
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
Can you please answer my questions?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,301
Messages
5,705,580
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top