[computerman29642]

I am trying to find a way to find a specific text within a worksheet, select that cell and the 9 rows under that row, and delete the 10 rows.

[computerman29642]

I forgot to mention that the code will actually run from a different worksheet.

So, sheet two will actual contain the code to delete the rows from sheet 1.

[turbodante]

Hey C'man.

try this...

Sub Macro1()
This = "0.00169909838200311" ' Change this value to whatever you want to search for
Rows(Cells.Find(What:=This).Row & ":" & Cells.Find(What:=This).Row + 9).Delete Shift:=xlUp
End Sub

You can have it on any woorkbnook you want and run it on anyworkbook you want also long as you Activate the workbook first before running it.

[turbodante]

As usual...

NB. Make backup of stuff before testing code

[computerman29642]

Thanks turbodante. Is there a way to change this portion of the code

& Cells.Find(What:=This).Row + 9).Delete Shift:=xlUp

to find the next blank cell?

Also, is there not a way to specify the worksheet within the code instead of activating the worksheet?

Something like this

 wsTest.Rows(Cells.Find(What:=This).Row

Where wsTest is set/assigned to a specific worksheet name.

[turbodante]

I don't understand.

• is the code deleting a row short,
• or do you want to include from the match to down 10 rows, plus additional to next blank

[computerman29642]

The code is falling a row short. There will always be a blank row betwene the rows being deleted, and the rows below the ones being deleted.

[turbodante]

Just so I understand...

If your match is in row 10, you want to delete rows

10,11,12,13,14,15,16,17,18,19 & 20 that's 11 rows?

[computerman29642]

That is correct. The reason for looking for the blank row is to be sure that all the rows in the correct range are selected.

I really did not want to specify teh number of cells because you never know if that number will increase.

[computerman29642]

turbodante, I believe I figured out the code. Please take a look below:

Rows(Cells.Find(What:=This).Row & ":" & Cells.Find(What:="", After:=ActiveCell).Row).Select

I used select for testing. What do you think?

[turbodante]

try this one...

It will delete from the matching row to the farthest blank row on the same column. NB. It may be that you have more than 1 blank, so it finds the blank before the next cell that contains a non-blank.

Sub Macro4()
Dim Rw1 As Long, Rw2 As Long
Set xSheet = Sheets("Sheet1")
This = "here is what you want to find"  ' change this to what you need to match
On Error Resume Next
   Rw1 = Cells.Find(What:=This).Row
   Rw2 = Cells.Find(What:=This).End(xlDown).End(xlDown).Offset(-1, 0).Row
 
xSheet.Rows(Rw1 & ":" & Rw2).Delete Shift:=xlUp
 
End Sub

[computerman29642]

Nevermind, the code I posted does not work properly after further testing.

turbodante, I am unable to get your new code to work.

[turbodante]

Originally Posted by computerman29642

...

turbodante, I am unable to get your new code to work.

What errors are you getting? Can you post a sample of your data?

[computerman29642]

I was finally able to get the code to work, but it is still selecting on row short.

[turbodante]

Originally Posted by computerman29642

I was finally able to get the code to work, but it is still selecting on row short.

Can you check if the very last blank cell truly blank. ie. is not " ", or a space character?