count rows till cell is blank
If you're looking for the last used cell in a column, then:
set lastcell = cells(rows.count,"A").end(xlup)
should work fine.
If you're getting what looks to be an empty cell as a result, I'm betting that
the users aren't clearing the cell (hitting the delete key or
edit|Clear|contents), I'm guessing that they may be hitting the spacebar
(multiple times???) to make the cell look empty.
If that's the case, then you should train them to use the delete key.
Otherwise, your code will have to find what you think is the last used cell and
then start eliminating the cells that contain those space characters.
Something like:
Option Explicit
Sub testme()
Dim LastCell As Range
Dim wks As Worksheet
Set wks = Worksheets("sheet1")
With wks
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
Do
If Trim(LastCell.Value) < "" Then
'found it
Exit Do
Else
If LastCell.Row = 1 Then
'no more to look for
Exit Do
Else
Set LastCell = LastCell.Offset(-1, 0)
End If
End If
Loop
End With
MsgBox LastCell.Address
End Sub
You may even want to clean up those cells with just spaces when your code
starts.
Option Explicit
Sub testme2()
Dim wks As Worksheet
Dim iCtr As Long
Set wks = Worksheets("sheet1")
With wks
For iCtr = 1 To 10 'as large as you think they'd use
.Cells.Replace what:=Space(iCtr), replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False
Next iCtr
End With
End Sub
=====
I've found that the real problem isn't in the code--it's in the formulas:
=if(a1="","onething","anotherthing")
will have to protect itself with something like:
=if(trim(a1)="","onething","anotherthing")
Junior728 wrote:
Hi,
How can i count the number of rows until i reached a blank cell and then
stop counting for a column? I know there is this xl.up count , but then it
counts that row even when it is blank. e.g.LastCell =
Cells(Rows.Count,"A").End(xlUp)
from what i know: the xl.up counts the last cell when the row is used
before. But sometimes if the cell content is cleared by someone in the
worksheet, the row is still counted under xl.up count.
Can anyone help?
--
Dave Peterson
|