Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
count rows till cell is blank
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
count rows till cell is blank
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
count rows till cell is blank
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
count rows till cell is blank
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
count rows till cell is blank
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
count rows till cell is blank
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
count rows till cell is blank
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count days in between or if not entered till TODAY | Excel Discussion (Misc queries) | |||
dates count till elapsed | Excel Discussion (Misc queries) | |||
Copy cell contents in empty rows below it till any unempty row com | Excel Discussion (Misc queries) | |||
Can I count down days till an event? | Excel Discussion (Misc queries) | |||
delete dulipate rows by checking each and every cell in a row till end. | Excel Programming |