Using Cell rather than Range

Posted by Noelle on June 15, 2001 6:33 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 doug on June 15, 2001 8:01 AM

sounds like a for loop to me ;) but i love for loops. seriously..try this..

sub test()
LastRow = ActiveSheet.Cells.SpecialCells _(xllastcell).row)

for x=1 to lastrow
h$=x
if range("A"+h$).value = "" then
alastrow=x
elseif range("B"+h$).value="" then
blastrow=x
elseif range("C"+h$).value="" then
clastrow=x
endif
'i think you get the idea ;)
next x
end sub

Posted by Damon Ostrander on June 18, 2001 1:24 PM

Hi Noelle,

You are on the right track, but just off a little on syntax. What you want is the Cells property, which takes two INTEGER arguments. So instead of Range("B6") or Cell(6,B) (which is bad syntax)

use Cells(6,2), where the 2 refers to the second column.

Damon