Delete Cells/Content from Row Number to End of Sheet 1

[waldemar]

I am looking for a way to delete all cells and all content from a specific row on to the end of the sheet... Does anyone knoe how to accomplish this? I appreciate any hint....

(This loop cycles through all rows of a certain sheet and is looking for the string "delete ever.....". locPort is a range that just points to a specific cell in the sheet header...)

For intRemoveCounter = locPort.row + 3 To locPort.Cells(65000, locPort.Column).End(xlUp).row
If InStr(1, Nz(rng.Cells(intRow, locRoom.Column), "&quot, "delete everything from here&quot > 0 Then

.....DELETE COMMAND (something with .entirerow? ??)

Exit For
End If
Next intRemoveCounter


Thanks in advance
waldemar

 

[xlbo]

Presumably you have found the start row
If that is then assigned to a variable eg stDelRow
then
rows(stDelRow & ":" & Cells(65000, locPort.Column).End(xlUp).row).entirerow.delete
should work quite nicely
Rgds
~Geoff~

 

[waldemar]

Wow thanks - perfect!

Unfortunately this arises a new problem.... later in the program I try to loop through a range that depends on the last entry-row of locPort.column (most of the times Column B).

The following loop worked ok before. Now it returns no row at all.My assumption is that there is something wrong the End(xlUp) thing... but I have no clue... do you???

For intRow = locPort.row + 3 To locPort.Cells(65000, locPort.Column).End(xlUp).row

 

[xlbo]

Let me make sure I am clear here.
1. Delet rows after a certain string is found
2. Loop through the remaining rows

If this is correct then, you do not need to reference LocPort in your end(xlup) statement

For intRow = locPort.row + 3 To Cells(65000, locPort.Column).End(xlUp).row Rgds
~Geoff~

 

[waldemar]

Perfect... thanks a lot!

 

[waldemar]

§@#!§#!!! Still a problem....

I am running this function several times for several files. Each time an Excel Object is declared and afterwards cleared (this worked fine before). However, including the row-delete causes an error the SECOND time this function is called: 1004 Method "rows" for object "_global" doesnt work.... any idea??

The code editor highlights:

Rows(intRemoveCounter & ":" & rng.Cells(65000, locPort.Column).End(xlUp).row).EntireRow.Delete

 

[waldemar]

found it.... i'm using more than one sheet so that reference missing. Rows(..... -> SomeRange.Rows(....