MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can VB identify & select dynamic ranges??


Posted by Tom Urtis on April 18, 2001 6:32 PM

In Excel 2000, I import rows of data (records) into the range A2:CZ300. The records vary in size every day, and along with the import comes useless junk starting 2 rows below the useful records which needs to be deleted. I'd like to know if VB code can identify the last row of a dynamic set of records by searching for the first blank cell in column A after A2, and once that cell (A125 or A whatever) is identified, make that cell part of the range (for instance) A125:CZ300, to then delete the contents in that range. Any suggestions? Thanks as always!!

Tom


Posted by Dave Hawley on April 18, 2001 8:39 PM


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

Posted by Tom Urtis on April 18, 2001 9:49 PM

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

Posted by Dave Hawley on April 18, 2001 10:12 PM

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

Posted by Tom Urtis on April 19, 2001 4:29 AM

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

Posted by Dave Hawley on April 19, 2001 4:51 AM

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

Posted by Tom Urtis on April 19, 2001 8:22 PM

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