Can VB identify & select dynamic ranges??

Hi Tom

If I have understood you, this should do the trick.

Sub TryThis()
Dim RStart As Range
Dim REnd As Range

Set RStart = Sheet1.Range("A2").End(xlDown)
Set REnd = Sheet1.Range("A65536").End(xlUp)

Range(RStart, REnd).EntireRow.Clear

Set RStart = Nothing
Set REnd = Nothing

End Sub

Dave

OzGrid Business Applications

Thank you Dave, almost but still 2 problems

After trying the code, I encountered two problems: (1) If all rows from A2:CZ300 are occupied with valid records (that is, no unoccupied row hence no unwanted junk below such unoccupied row, which might be the case) then this code deletes all records, at least the way I tried it. (2) This code deletes the entire row when only the range A whatever to CZ300 is what I hope to be subject to deletion. Thanks again though; I'll keep playing with it and this is a step in the right direction but I'm not quite there yet. Tom

Re: Thank you Dave, almost but still 2 problems

Ok, I think I understand you now.

Sub TryThis()
Dim RStart As Range
Dim REnd As Range

Set RStart = Sheet1.Range("A2").End(xlDown).Offset(2, 0)
Set REnd = Sheet1.Range("A65536").End(xlUp)

If RStart.Row < 300 Then
Range(RStart, REnd).Range("A1", "CZ" & _
Range(RStart, REnd).Rows.Count).Clear
End If

Set RStart = Nothing
Set REnd = Nothing

End Sub

Dave

OzGrid Business Applications

Getting closer but still not there, problems

Hi Dave, we're getting close but still having difficulties, primarily that this code deletes data below row 300 (in columns A:CZ), which is an unwelcome result because there will be data below row 300 in all columns that I definitely need to stay untouched.

Second, a minor change in my workbook from my original post (my IT guys say the final change), I for need the code to:
(1) Search for the first blank cell after A2, in range A2:A300.
(2) If no blank cell is located, then nothing else needs to be done, and the code stops.
(3) If a blank cell is located (which will be the case 99% of the time), select the range A?:CZ300, where A? equals the cell immediately above the blank cell that was found. Then the contents of that range are deleted.

Example: On today's sheet, the first blank cell (starting with A2 and moving downwards), happens to be A236. The code selects range A235:CZ300, and deletes the contents in that range only.

Notice that my goal is to delete the contents in the last row of data that is immediately above the row belonging to the first blank cell in column A after A2. And of course I also want to delete the contents in all rows after that up to & including (but not exceeding) row 300, and up to & including (but not exceeding) column CZ.

I hope this clarifies what I'm needing, thank you again for any direction on this.

Tom Urtis

Third time lucky ?

Hi Tom

Give this a run!

Sub TryThis()
Dim RStart As Range
Dim REnd As Range

Set RStart = Sheet1.Range("A2").End(xlDown)
Set REnd = Sheet1.Range("CZ300")

If RStart.Row < 300 Then
Range(RStart, REnd).Clear
End If

Set RStart = Nothing
Set REnd = Nothing

End Sub

Dave

OzGrid Business Applications

Third time's the charm!! Thanks Dave that's it!!

Give this a run! Sub TryThis()

Thanks Dave, you pulled me out of a jam. Have a great day. Tom