Amending code to go to last row of a table

[STEVENS3010]

Hi all,

I have used the macro recorder to apply a table to some information in a sheet. At present it is using a specific range; could anybody help me amend the code to go to the last row instead as the data will not be the same number of rows every time? The current code from the recorder is...

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$6:$F$20"), , xlYes).Name = _
        "Table3"
    Range("Table3[#All]").Select
    ActiveSheet.ListObjects("Table3").TableStyle = "TableStyleLight19"
    Range("A7").Select

 

[Damian37]

Hi all,

I have used the macro recorder to apply a table to some information in a sheet. At present it is using a specific range; could anybody help me amend the code to go to the last row instead as the data will not be the same number of rows every time? The current code from the recorder is...

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$6:$F$20"), , xlYes).Name = _
        "Table3"
    Range("Table3[#All]").Select
    ActiveSheet.ListObjects("Table3").TableStyle = "TableStyleLight19"
    Range("A7").Select

I've typically used this to locate the last row in my data sheet:

Dim lRow As Long
Dim WS As Worksheet
Set WS = Worksheets("Name of Worksheet searching for last row")

lRow = Ws.Cells.FInd(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

Hope this helps!
D.

 

[MARK858]

Try the code below to see if it gives you the row number of the last row in the table...

Sub Macro1()

    Dim TblLastRow As Long
    
    With ActiveSheet.ListObjects("Table3")
        TblLastRow = .DataBodyRange.Rows.Count + .HeaderRowRange.Row
    End With
    MsgBox TblLastRow
End Sub

 

[STEVENS3010]

Hi thanks both, I’m not looking to find out the last row number. I’m looking to format a range of data to a table, but rather than using a specific range A6:F20, just apply the table (including the table style) to the last row in the range.

 

[MARK858]

The code I posted gives you the last row number of the table. If you know that then you can apply that to the range for the table i.e.

Sub Macro1()
    Dim TblLastRow As Long

With ActiveSheet.ListObjects("Table3")
TblLastRow = .DataBodyRange.Rows.Count + .HeaderRowRange.Row
End With
Range(Cells(TblLastRow, "A"),Cells(TblLastRow, "F").Select
End Sub

just apply the table (including the table style) to the last row in the range.

To apply a table to the last row in a range then maybe..

Sub Macro1()

    Dim RngLastRow As Long
    
    With Range("$A$6:$F$20")
        RngLastRow = .Rows(.Rows.Count).Row
    End With
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(RngLastRow, "A"), Cells(RngLastRow, "F")), , xlYes).Name = _
                                                                                                                 "Table3"
    ActiveSheet.ListObjects("Table3").TableStyle = "TableStyleLight19"
End Sub

 

[STEVENS3010]

Really appreciate you replying. The issue I have is that the range of data specifically the number of rows will change every time the macro is applied, therefore I need the code to be dynamic and to not use a static range. Does that make sense?

 

[MARK858]

Does that make sense?

No because you haven't stated how you are defining the range. If it is continuous data from A6 to F20 with an empty column and row afterwards (and row 5 empty) then you can use

Sub Macro1()

    Dim RngLastRow As Long
  
    With Range("$A$6").CurrentRegion
        RngLastRow = .Rows(.Rows.Count).Row
    End With
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(RngLastRow, "A"), Cells(RngLastRow, "F")), , xlYes).Name = _
                                                                                                                 "Table3"
    ActiveSheet.ListObjects("Table3").TableStyle = "TableStyleLight19"
End Sub

Other than that we need more detail.

 

[STEVENS3010]

Ok, sorry, let me try to be more clear.

So I have a macro that will paste a range of data into a sheet. The range will be pasted into cell A7 across to F7 and then down a varying number of rows (this will be different every time the macro is run - daily report)

When the data is pasted into the sheet, I would like the macro to apply the table formatting ("TableStyleLight19") for the whole range. The code the recorder has given me uses a static range (A6:F20) but I would like to amend the code so that the macro does not use a static range, but applies the formatting to however many rows are in the range. e.g. one day it could be 10 rows of data, the next day it could be 20 rows of data.

 

[MARK858]

If that is all that is on the sheet then you possibly only need...

Sub Macro1()

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A7").CurrentRegion, , xlYes).Name = "Table3"
    ActiveSheet.ListObjects("Table3").TableStyle = "TableStyleLight19"
End Sub

Based on you stating A7 now rather than A6.

 

[STEVENS3010]

That's it! Brilliant - thanks so much!