Select last cell in a certain column in a table

[robtperk]

I want to create a function returning the count of contiguous non-blank/non-zero cells at the bottom of a column in a table.

My thoughts are to first select the bottom cell in the "Table1" in the column named "Rem". Starting there, I would loop through each cell above, increasing a counter by 1, until I came to a non-zero value cell (there will be only blank or zero values in the 'blank' cells, no text or formulas).

How do I select that bottom cell in the named column?

I'm using Office 365.

TIA,
robtperk

 

[mrmmickle1]

Try something like this:

[/COLOR][COLOR=#0000ff]For [/COLOR]LngInt = LastRow [COLOR=#0000ff]To[/COLOR] 2 [COLOR=#0000ff]Step [/COLOR]- 1

[COLOR=#0000ff]If[/COLOR] Cells(LngInt, YOURCOLUMN) = ""  [COLOR=#0000ff]Then[/COLOR]
[COLOR=#008000]'Do Something here[/COLOR]
[COLOR=#0000ff]End If[/COLOR]

Counter = Counter + 1

[COLOR=#0000ff]Next [/COLOR]LngInt

 

[robtperk]

Thanks, that will do help with the last bit. I thought that selecting the last cell in the particular column would be difficult but it works with this:

Sub Macro3()    Application.Goto Reference:="TblData"
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 4).Select 'Column 4 will always be in the one I need to test for this app
End Sub

I think I can even use the ActiveCell.Offset method in my loop, starting off with setting the value of LastRow to the row number of the selected cell

 

[mrmmickle1]

Glad you were able to get it all worked out.

Try this out if your always using column D:

Range("D1").End(xlDown).Offset(0, 4).Select

 

[MARK858]

If there is any chance of blank cells in the column then try one of the options below

Range("D" & Rows.Count).End(xlUp).Offset(0, 4).Select

or

Cells(Rows.Count, "D").End(xlUp).Offset(0, 4).Select

or

Columns(4).Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Offset(0, 4).Select

or

If using the activecell as you did...

Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(0, 4).Select

although you probably don't need the select but we don't know what comes next in your code

 

[robtperk]

Thanks all. Column A will never contain a blank - only dates, but the above suggestions would be helpful if that weren't the case.
What I was doing was creating a way to do a forecast of remaining cost using a normal distribution instead of straight-lining it. I needed to know the number of periods to spread the remaining cost, and the code that worked is:

Sub GetNumberOfPeriods()    Dim iPeriods As Integer, iRows As Integer, i As Integer, sName As String, iCol As Integer
    sName = "tblCurvCalcs"
    iCol = 3 'Need column 4 but offset starts with 1 instead of 0
    iRows = Range(sName).Rows.Count
    
    'First select the bottom cell in the Actual column
    Application.Goto Reference:=sName
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, iCol).Select
    
    For i = 1 To iRows - 1
        If ActiveCell.Value = 0 Or ActiveCell.Value = "" Then
            iPeriods = iPeriods + 1
        End If
        ActiveCell.Offset(-1, 0).Select
    Next i


   ActiveSheet.Range("nPeriods") = iPeriods
End Sub

 

[mrmmickle1]

I think you can get the same end result with this simple formula:

Excel 2013
	D	E	F
1	Header	Nperiods	13
2
3	0
4	0
5	0
6	0
7	0
8	0
9
10
11
12	6
13	6
14
15	6
16	6
17
18	0
19	6
20	6
Sheet1

Cell Formulas
Range	Formula
F1	=COUNTBLANK(D2:D20)+ COUNTIF(D2:D20,0)