Is it possible to find the number of rows that have data in them and then use that number to create a DO WHILE loop using that number?

This statement will find the range of the cells, but can I used the last part of that formula to TELL me how many actual rows there are and then convert that into a number (ACTROWNUM)I can use?

For lRow = 1 To Sheets("P_Days").Cells(Rows.Count, 1).End(xlUp).Row

My goal is to use a loop that says something like this:

Do while iNumRows <= ActRowNum

Can Excel do this with Columns as well? I.e. Find the actual number of columns with data in them?

Can Excel convert that number to something I can create a DO Loop with? Same principle as above – go until the last column of data is hit.

XlLeft doesn’t seem to work or I am using it incorrectly but I can use CTRL Left Arrow to move to the last Column with data it so I assume there is an Excel command somewhere.

Richard Hughes

A1Pilot@Flica.net

Hi Bill

I think I can see my errors here.

I left teh FOR in front of the statement. I'm not trying to define the range by setting the number. I think I got it.

Syntax is everything. Yup I forgot the TO in the xlTOleft. I'm sure there is a reason why there is a TO there but I don't know why.

Which do you think is better the DO While or the FOR loop. Yes I will be increasing by 1 everytime the code gets to the bottom of the row. I don't have a favorite.. it's just which ever I can get to work at the time.

Unfortunately the data columns are NOT continious, there are blank columns in between the columns with data. So I will have to test for a blank cell and move to the next column if it find the blank.

Thanks again for your help you have saved my butt on more than one occasion.

Richard Hughes
A1pilot@flica.net

Which do you think is better the DO While or the FOR loop. Yes I will be increasing by 1 everytime the code gets to the bottom of the row.

I would slightly prefer the For loop in that case (because it is designed for that circumstance).
For one thing, it is easy to forget to increment the loop counter in a Do loop and you end up with an endless loop.

I'm sure there is a reason why there is a TO there but I don't know why.

xlLeft is a constant (-4131) used to select a left-hand position for things (a chart legend, a summary column or, in earlier versions, a left-hand border).
xlToLeft is a constant (-4159) used with End to mean a direction of travel and with Delete to indicate a shift direction.

I guess the people who chose those values about 18 years ago didn't think that they were sufficiently similar that using the same name (and therefore constant value) was appropriate, and from the numbers I would think that the programmer who defined xlLeft got there first.

Bill Manville
Excel MVP/TA
Oxford, England