Cells

Posted by Noelle on June 15, 2001 6:30 AM

I am having a problem with an excel macro. I am trying to check a column of cells till I come to a blank cell. Then when I come across the blank cell that is the end of the data. Record this row number and then loop through other columns to check for blank between the first record and the final record which will have been recorded earlier. However, the only syntax I can get is for Range("B6") rather than Cell(6, B). The reason I need it by cell is to increment the row by one.

Posted by Cory on June 15, 2001 9:08 AM

I think your problem lies in getting to the cell underneath the last cell containing data (the first blank cell). To select the first empty cell and its corresponding row, try the following:

Say you're starting with the top of column A:

Range("A1").Select 'starts with cell A1
Selection.End(xlDown).Select 'to end of data
ActiveCell.Offset(1,0).Rows("1:1").EntireRow.Select

I can't figure out how to get Excel to enter the number of that row into another cell, but maybe this will be enough to get you started.

Cory

Posted by Sean on June 15, 2001 2:32 PM

==========
Noelle, Try putting the following code into your VBA module...

Sub Chkdat()

Range("A1").Select 'starts with cell A1
Selection.End(xlDown).Select 'to end of data
lr = ActiveCell.Row

For rw = 1 To lr
For cl = 2 To 5 ' I have assumed your table width is 5 columns including Col A
If Cells(rw, cl).Value = "" Then
' do what ever you want with a blank cell in your
' table ... eg set value to XXX.
Cells(rw, cl).Value = "XXX"
End If
Next
Next

End Sub

I am not exactly sure what you want to do once you have identified any blank cells in the table but the above code will find them and for want of anything better I have set them all to the value XXX.

Let me know if this helps

Sean.