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?

Hi Junior.

The expression:

> Cells(Rows.Count,"A").End(xlUp)

Returns the last visible, populated cell in column A.

To return the last populated cell in the range of interest,
visible or not, try the following function:

'=============>>
Public Function LastCell(SH As Worksheet, _
Optional Rng As Range) As Range

If Rng Is Nothing Then
Set Rng = SH.Cells
End If

On Error Resume Next
Set LastCell = Rng.Find(What:="*", _
After:=Rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
On Error GoTo 0
End Function
'<<=============


For example:

'=============>>
Public Sub TestIt()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim Rng2 As Range

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

With SH
Set Rng = LastCell(SH, .Columns("A:A"))
Set Rng2 = LastCell(SH)
End With

If Not Rng Is Nothing Then
MsgBox Rng.Address
End If

If Not Rng2 Is Nothing Then
MsgBox Rng2.Address
End If

End Sub
'<<=============


---
Regards,
Norman


"Junior728" <(E-Mail Removed)> wrote in message
news:6EC209AB-0B78-4C18-9B63-(E-Mail Removed)...
> 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?
>
>
>

Is this what you waht?

Sub CountToBlank()
Dim myRange As Range

Set myRange = Cells(1, 1)
Do While (Not IsEmpty(myRange)) And myRange.Row < Rows.Count
Set myRange = myRange.Offset(1, 0)
Loop
Debug.Print "First Blank Cell is at address: " & myRange.Address
End Sub

"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?
>
>
>

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

Hi,

Thanks for the inputs. However,this is what my code appears below...i wish
to copy a formula for a selected range cell that starts from Cell C25 to end
of lastcell for that column(column C)?

How can i do that? FYI, i use the record macro to come up with these code
below:

=====================================================
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C25").Select
ActiveCell.FormulaR1C1 = "=IF(TRIM(RC[-1])=TRIM(R[1]C[-1]),1,2)"
Range("C25").Select
Selection.Copy
Range("C26:C64").Select ' last cell does not always end at Cell 64.
ActiveSheet.Paste


"Dave Peterson" wrote:

> 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
>

I used column B to determine what that lastrow should be for column C:

Dim LastRow as long
with activesheet
.range("C1").entirecolumn.insert
lastrow = .cells(.rows.count,"B").end(xlup).row
.range("C25:c" & lastrow).formular1c1 _
= "=IF(TRIM(RC[-1])=TRIM(R[1]C[-1]),1,2)"
end with

If column B shouldn't be used, pick out a column that can be used--or share how
that lastrow should be determined.

Junior728 wrote:
>
> Hi,
>
> Thanks for the inputs. However,this is what my code appears below...i wish
> to copy a formula for a selected range cell that starts from Cell C25 to end
> of lastcell for that column(column C)?
>
> How can i do that? FYI, i use the record macro to come up with these code
> below:
>
> =====================================================
> Columns("C:C").Select
> Selection.Insert Shift:=xlToRight
> Range("C25").Select
> ActiveCell.FormulaR1C1 = "=IF(TRIM(RC[-1])=TRIM(R[1]C[-1]),1,2)"
> Range("C25").Select
> Selection.Copy
> Range("C26:C64").Select ' last cell does not always end at Cell 64.
> ActiveSheet.Paste
>
> "Dave Peterson" wrote:
>
> > 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
> >

--

Dave Peterson

Hi Dave,

I tried it but somehow it does not start from row 25. Does your code always
go to Range C1 by default? (I have other information from row 1 to 24 that i
do not want to apply the formula on).



"Dave Peterson" wrote:

> I used column B to determine what that lastrow should be for column C:
>
> Dim LastRow as long
> with activesheet
> .range("C1").entirecolumn.insert
> lastrow = .cells(.rows.count,"B").end(xlup).row
> .range("C25:c" & lastrow).formular1c1 _
> = "=IF(TRIM(RC[-1])=TRIM(R[1]C[-1]),1,2)"
> end with
>
> If column B shouldn't be used, pick out a column that can be used--or share how
> that lastrow should be determined.
>
> Junior728 wrote:
> >
> > Hi,
> >
> > Thanks for the inputs. However,this is what my code appears below...i wish
> > to copy a formula for a selected range cell that starts from Cell C25 to end
> > of lastcell for that column(column C)?
> >
> > How can i do that? FYI, i use the record macro to come up with these code
> > below:
> >
> > =====================================================
> > Columns("C:C").Select
> > Selection.Insert Shift:=xlToRight
> > Range("C25").Select
> > ActiveCell.FormulaR1C1 = "=IF(TRIM(RC[-1])=TRIM(R[1]C[-1]),1,2)"
> > Range("C25").Select
> > Selection.Copy
> > Range("C26:C64").Select ' last cell does not always end at Cell 64.
> > ActiveSheet.Paste
> >
> > "Dave Peterson" wrote:
> >
> > > 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
> > >
>
> --
>
> Dave Peterson
>

Nope, but my code doesn't check to see where the lastrow based on column B is.

If you can't trust column A to have data, can you trust a different column?

This adds a test for that lastrow.

Dim LastRow as long
with activesheet
.range("C1").entirecolumn.insert
lastrow = .cells(.rows.count,"B").end(xlup).row
if lastrow < 25 then
msgbox "Not enough rows to fill!
exit sub
end if
.range("C25:c" & lastrow).formular1c1 _
= "=IF(TRIM(RC[-1])=TRIM(R[1]C[-1]),1,2)"
end with



Junior728 wrote:
>
> Hi Dave,
>
> I tried it but somehow it does not start from row 25. Does your code always
> go to Range C1 by default? (I have other information from row 1 to 24 that i
> do not want to apply the formula on).
>
> "Dave Peterson" wrote:
>
> > I used column B to determine what that lastrow should be for column C:
> >
> > Dim LastRow as long
> > with activesheet
> > .range("C1").entirecolumn.insert
> > lastrow = .cells(.rows.count,"B").end(xlup).row
> > .range("C25:c" & lastrow).formular1c1 _
> > = "=IF(TRIM(RC[-1])=TRIM(R[1]C[-1]),1,2)"
> > end with
> >
> > If column B shouldn't be used, pick out a column that can be used--or share how
> > that lastrow should be determined.
> >
> > Junior728 wrote:
> > >
> > > Hi,
> > >
> > > Thanks for the inputs. However,this is what my code appears below...i wish
> > > to copy a formula for a selected range cell that starts from Cell C25 to end
> > > of lastcell for that column(column C)?
> > >
> > > How can i do that? FYI, i use the record macro to come up with these code
> > > below:
> > >
> > > =====================================================
> > > Columns("C:C").Select
> > > Selection.Insert Shift:=xlToRight
> > > Range("C25").Select
> > > ActiveCell.FormulaR1C1 = "=IF(TRIM(RC[-1])=TRIM(R[1]C[-1]),1,2)"
> > > Range("C25").Select
> > > Selection.Copy
> > > Range("C26:C64").Select ' last cell does not always end at Cell 64.
> > > ActiveSheet.Paste
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 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
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson