[RESOLVED] [Excel 2000/2003] Spreadsheet cell location

Okay, what I'm trying to do.

Need to be able to run a loop that checks contents of cells or lack thereof.

That's the easy part.

My problem comes from the fact that I have to have the macro start at A1 and work down the column from there, with A1 and an unspecified number of cells below it empty before I reach the first "live" cell. Once I reach that cell, everything is fine.

macro Code:

Sub dataFill()
  Sub dataFill()
    Dim strIs, strFill As String
    Dim i As Integer
    i = rngCount()
    Range("A1").Select
    
    Do While i <> 0
        strIs = ActiveCell.Value
        
        If strIs = "" Then
            ' if ActiveCell position is A1 then
            '     ActiveCell.Offset(1,0).select
            '     i=i-1
            ' else
            '     strFill = ActiveCell.Offset(-1,0).value
            '     ActiveCell.Value=strFill
            '     ActiveCell.Offset(1,0).select
            '     i=i-1
            ' end if
        Else
            ActiveCell.Offset(1, 0).Select
            i = i - 1
        End If
    Loop
End Sub

there are several ways to get the first active row

vb Code:

frow = sheets("sheet1").usedrange.row
  ' loop only rows in used range, if any column in row 1 has data frow will be 1
lrow = sheets("sheet1").usedrange.rows - frow
for r = frow to lrow

or

vb Code:

frow = range("a1").end(xldown).row
lrow = range("A65535").end(xlup).row
'  will get the first and last used row of specific column

you should avoid selecting each cell, just loop through the cells by address

vb Code:

dim c as range
for each c in range("a" & frow & ":a" & lrow)
  if isempty(c) then c = c.offset(-1,0)
' not sure that this is what you actually want to do but may help get the general idea
next

Quote:

---

Originally Posted by westconn1

there are several ways to get the first active row

vb Code:

frow = sheets("sheet1").usedrange.row
  ' loop only rows in used range, if any column in row 1 has data frow will be 1
lrow = sheets("sheet1").usedrange.rows - frow
for r = frow to lrow

or

vb Code:

frow = range("a1").end(xldown).row
lrow = range("A65535").end(xlup).row
'  will get the first and last used row of specific column

you should avoid selecting each cell, just loop through the cells by address

vb Code:

dim c as range
for each c in range("a" & frow & ":a" & lrow)
  if isempty(c) then c = c.offset(-1,0)
' not sure that this is what you actually want to do but may help get the general idea
next

---

All snippets look like they would work and I intend to file the other two for possible future use, but solution #2 as a function works perfectly.

As always westconn your solutions are fantastic.

Thak you

datapard