Trying to find last row a column C

[Eric Penfold]

Please see code below to find last row in column c in a Excel Table. How can I alter the code to work?

LRow = tblDailyMail.Cells(Rows.Count, 3).End(xlUp).Row

Sub Mouthly_DailyMail_Figurers()

    Dim CopyToRange As Range
    Dim PasteToRange As Range
    Dim Rng    As Range
    Dim Cell   As Range
    Dim LRow   As Long
    Dim wb     As Workbook
    Dim swb    As Workbook
    Dim sws    As Worksheet
    Dim ws     As Worksheet
    Dim tblDailyMail As Object
    Dim FileToOpen As Variant

    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .DisplayAlerts = False
    End With
   
    Set swb = ActiveWorkbook
    Set sws = swb.Worksheets("Sheet")
    FileToOpen = ("S:\SALES\REPORTING\2023\Daily Mail.xlsx")
    Workbooks.Open FileToOpen
    Set wb = Workbooks("Daily Mail.xlsx")
    Set ws = wb.Worksheets("Daily Mail Update")
    Set CopyToRange = sws.Range("A2")
   
    With ws
        Set tblDailyMail = .ListObjects("Daily_Mail_Data")
        LRow = tblDailyMail.Cells(Rows.Count, 3).End(xlUp).Row
        Set PasteToRange = tblDailyMail.Range("C5:C" & LRow)
        For Each Cell In PasteToRange
            If Not WorksheetFunction.IsText(Cell.Value) Then
                Cell.Value = CopyToRange.Value
                Cell.Font.Name = "Arial"
                Cell.Font.Size = 11
            End If
        Next Cell
        .Range("H25") = CopyToRange
        .Range("I25") = sws.Range("B2")
    End With
   
    Call DalyMailUpdate
   
    Workbooks("Daily Mail.xlsx").Save
    '    Workbooks("Daily Mail.xlsx").Close
   
    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .DisplayAlerts = True
    End With
   
    MsgBox ("Daily Mail Mountly Figurers Updated")
   
End Sub

 

[Legacy 143009]

Hi,

Since you are explicitly addressing a table, how about looping directly in that column?

        'LRow = tblDailyMail.Cells(Rows.Count, 3).End(xlUp).Row
        'Set PasteToRange = tblDailyMail.Range("C5:C" & LRow)
        For Each Cell In tblDailyMail.listcolumns("columnCHeaderName").databodyrange
        .
        .
        Next

 

[MARK858]

Assuming by Column C you mean the 3rd column in the table then maybe try the ugly code below

    With tblDailyMail
        LRow = Cells(.ListColumns(3).Range.Rows.Count + .HeaderRowRange.Row - 1, .ListColumns(3).Range.Column).End(xlUp).Row
    End With

 

[Eric Penfold]

Hi,

Since you are explicitly addressing a table, how about looping directly in that column?

        'LRow = tblDailyMail.Cells(Rows.Count, 3).End(xlUp).Row
        'Set PasteToRange = tblDailyMail.Range("C5:C" & LRow)
        For Each Cell In tblDailyMail.listcolumns("columnCHeaderName").databodyrange
        .
        .
        Next

This works thanks

 

[Legacy 143009]

Glad it did work! Thanks for the feedback