[computerman29642]

Yes, it is truly blank.

[turbodante]

I am stumped...

Without seeing the error, i can;t figure out what's happening and can only go on what I think could be a representitive dataset to test on.

My Solution would be to change the line

Rw2 = Cells.Find(What:=This).End(xlDown).End(xlDown).Offset(-1, 0).Row

to

Rw2 = Cells.Find(What:=This).End(xlDown).End(xlDown).Row

[computerman29642]

That appears to have worked. How does the code know to stop at the blank row?

Can you explain the code?

[computerman29642]

The code actually selects (I am using select for testing to be sure before I use delete), one row before the blank row. Is there a way to get it to select the blank row as well.

This is so that when the delete is in place there is not two blank rows before the next set of data.

[turbodante]

Glad it worked - here's the code again with comments.

Sub Macro4()
Dim Rw1 As Long, Rw2 As Long 'declare variables to avoid bugs
Set xSheet = Sheets("Sheet1") ' reference the sheet for the code
This = "here" ' Item that you want to find
On Error Resume Next ' On occasions where Item is no found, put this in otherwise you'll have a error - take the line out and see!
   Rw1 = Cells.Find(What:=This).Row ' This should be the row if there's a match, store the Row number in Rw1
   Rw2 = Cells.Find(What:=This).End(xlDown).End(xlDown).offest(-1,0).Row ' This is the end row, which moves downwards from the found match, move downwards again because first time round will hit the last non-empty - second time round will be out of that conseutive non-emtpty range and move onto the last empty cell
 
xSheet.Rows(Rw1 & ":" & Rw2).Delete Shift:=xlUp ' use the variable as found in the above variables in the delete
 
End Sub

Let me know if you need further explaination, though I'm pretty sure someoneelse can come up with a more elegant/efficient way to do it.

[turbodante]

Originally Posted by computerman29642

The code actually selects (I am using select for testing to be sure before I use delete), one row before the blank row. Is there a way to get it to select the blank row as well.

This is so that when the delete is in place there is not two blank rows before the next set of data.

Change it back to the orignal Rw2 line.

[computerman29642]

I am still confused as to how the .End(xlDown) knows where to stop. What determines the "end"?

[turbodante]

.End(xlDown)

Will go in the direction until it encounters a cell in which it's contents (empty/non-empty) are opposite to which it started.

Direction can be ...

(xlUP)
(xlLeft)
(xlRight)

[computerman29642]

I am still confused, but I will do more research.

Thanks turbodante.

[computerman29642]

All was working well until I try using the code on the real Excel file. Here is the code I am using:

Dim Rw1 As Long, Rw2 As Long, xActiveSh As String, wsSheet As Worksheet
    Dim FindString As String

    Set wsSheet = Sheets("Sheet1")
    xActiveSh = ActiveSheet.Name

    FindString = xActiveSh

    wsSheet.Activate

    Rw1 = Cells.Find(What:=FindString).Row
    Rw2 = Cells.Find(What:=FindString).End(xlDown).End(xlDown).Row
 
    Rows(Rw1 & ":" & Rw2 + 1).Select '.Delete Shift:=xlUp

Below is the error I am getting:

Run-time error '91': Object variable or With block variable not set

The error occurs on this line of code

Rw1 = Cells.Find(What:=FindString).Row

[computerman29642]

I am trying to run teh code from a command button. When I do so, I get the erro rmessage stated in post #25. However, when I run teh code from amodule, it appears to work.

Why?

[computerman29642]

I have it working for now. I placed the code in a module, and performed a CALL command within the command button.

[Zack Barresse]

I would do two Find methods. Set the second Find to look after (using the After syntax) the first one found and using xlNext. Then you can test the row to ensure the second range row is greater than the first range row. I would set the Find range to look in the entire desired column. And if it's a bunch of blank cells, set the second Find to find the next cell with a value, then offset it by -1 rows (doing your row check between first and second range after the offset, I'd just build the offset into the second Find actually).

Does that make sense? That way you're not left using the xlDown, which can be risky if you don't know where data will lie, IMHO.

[computerman29642]

I tried using the After syntax, but could never get it to work properly.

I am not really sure what you mean by "build the offset into the second Find.

I will playaround somemore, and see if I can get the second Find to work.

[Zack Barresse]

Just got a quick sec, so I'll post some untested code to give you an idea...

dim r1 as range, r2 as range
set r1 = ws.columns(1).find(what:="something", after:=ws.cells(1, 1))
set r2 = ws.columns(1).find(what:="something2", after:=r1).offset(-1, 0)
if r2.row <= r1.row then
    'bad range
else
    'good range, use range here...
    ws.range(r1, r2).entirerow.delete
end if

Something like that. Make sense?

So if your data was something like...

Data1
data2
data3
...blank
...blank
...blank
data4

... if you looked for "data3" for r1 and "data4" for r2, then it would delete from "data3" to the cell directly above "data4". I think that's what you're asking for.

What that doesn't take into account is if "something" or "something2" wasn't found, which you could test for with the If r2 Is Nothing type clause.

Edit: Some clarification.

HTH