specifying a range of cells

I have the following code for working with an excel spreadsheet (obviously this is only part of the code):

AAA:
For Row1 = 3 To 300
With XLJob.Worksheets(2).Cells(Row1, 1)
If XLJob.Worksheets(2).Cells(Row1, 1) = "" Then
XLJob.Rows(Row1).Delete
End If
End With
Next Row1


For Row2 = 1 To 300
With XLJob.Worksheets(2).Cells(Row2, 1)
If XLJob.Worksheets(2).Cells(Row2, 1) = "" Then
GoTo AAA
End If
End With
Next Row2

To summarise, Row1 looks at each Row between 1 and 300 and deletes any blank rows. After that, Row2 relooks at each Row and if it encounters a blank row it reloops back to Row1 (so it can be deleted).

However, in the Row2 "For..Next" loop instead of saying:

If Row is blank, goto AAA

I want to be able to say:

If Row is blank AND if the are any rows between Row and row 2000 that ARE NOT blank, then goto AAA.

(Currently, say there are only 250 entries. The loop gets to row 251 and just loops forever. So I want my loop to be able to recognise the end of the recordset.)

Does anybody know how to do this?

Your help is much appreciated.

Look up help on Range command. That will let you specify, oddly enough, a range of cells. How you do a comparision to that is unknown to me but the HELP should be able to assist again.

HTH

Quote of the moment....
"My job is so top secret even I don't know what I'm doing!"

Thanks once again for your time Valkyrie.

I've already looked up the help files. It showed me how to specify a static range of cells:

Worksheets(1).Range("A1:A2000")

However, it doesn't give any examples on how to specify a relative range ie from row "Row" to A2000:

Worksheets(1).Range("Row:A2000")

mojo

From my experience there are 2 ways to deal with Excel cells. You can refer to them as letters or as numbers, so A1 is also 1,1. Again, not being the wizard I will refer you to the help files.

In regards to using a variable simply take the quote marks away from your variable name and put it around the rest of it. If your range is always Column A then you can use the letternumber notation, if not you can use the numbernumber notation I referred to above.

e.g. Worksheets(1).Range("A" & Row & ":A2000")

if row = 250 then that should resolve itself to Worksheets(1).Range("A250:A2000")

HTH /images/icons/smile.gif


Quote of the moment....
"My job is so top secret even I don't know what I'm doing!"

Valkyrie

Often this programming stuff is so simple you (read: I)overlook it.

Thanks for the advice....it worked perfectly.

mojo

Excellent....sometimes it's hard to see the forest for the tree's. I'm trying to get my head around Visual Age for Java....now THAT is a different thought process. I'm used to doing JAVA on notepad so going to a GUI certainly seems.....gooey!

Cheers


Quote of the moment....
"My job is so top secret even I don't know what I'm doing!"