How can I select data to the end of a range in a macro?

**warpdesign** · 02-03-2015, 01:24 PM

I want my macro to always select all the data in a range. The problem is if I specify a range it will always select that exact range, not the data itself. I've tried converting it to a table but the same problem occurs, it will always do the same range of cells instead of the actual data.

**TMS** · 02-03-2015, 01:32 PM

Option Explicit

Sub sLastRow()

Dim lLR As Long
lLR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & lLR).Select

' or just

Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Select

' or

Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).Select

End Sub

Regards, TMS

**warpdesign** · 02-03-2015, 01:48 PM

Hi,
Here is a good example, it looks like when it comes to the selection, maybe it is selecting to the end of the data, but when it does the command to format the selection as a table it explicitly does the range in the example file I used to setup the macro. I think what needs to be edited is the Range() parameter in the .Add section that creates Table1:

Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$120"), , xlYes).Name = _
        "Table1"

**TMS** · 02-03-2015, 02:24 PM

No, that's not a good example, it's a different example.

If you have a Structured Table, Table1, and you want to select the data, you can use:

Range("Table1").select

But, in practical terms, you should avoid selecting anything. In the majority of cases, it isn't necessary.

Regards, TMS

**warpdesign** · 02-03-2015, 02:36 PM

No, I'm not trying to select the table, here I am creating the table. I select to the end of the data and then click format as table, but the macro interprets that as converting a literal range. The next time I run this macro on a file it will convert A1 to F120 to a table instead of all the data. I think I need to replace Range("$A$1:$F$120") with something that would always capture the entire range.

**TMS** · 02-03-2015, 03:46 PM

Sub sRngAddress()
Dim lLR As Long, lLC As Long
lLR = Cells(Rows.Count, 1).End(xlUp).Row
lLC = Cells(1, Columns.Count).End(xlToLeft).Row
With Sheets("Sheet1")
    .ListObjects.Add( _
                xlSrcRange, _
                .Range(.Cells(1, 1), .Cells(lLR, lLC)), , _
                xlYes).Name = "Table1"
End With
End Sub

BUT, you're not going to be able to run the macro more than once because the Table will already exist and you won't be able to overlap a table.

**TMS** · 02-03-2015, 04:34 PM

Avoiding the problem ...

Sub sRngAddress()
Dim lLR As Long, lLC As Long
lLR = Cells(Rows.Count, 1).End(xlUp).Row
lLC = Cells(1, Columns.Count).End(xlToLeft).Row
With Sheets("Sheet1")
    On Error Resume Next
    ' remove Table1 if it is has already been added
    .ListObjects("Table1").Unlist
    On Error GoTo 0
    .ListObjects.Add( _
                xlSrcRange, _
                .Range(.Cells(1, 1), .Cells(lLR, lLC)), , _
                xlYes).Name = "Table1"
End With
End Sub

Regards, TMS

How can I select data to the end of a range in a macro?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How can I select data to the end of a range in a macro?

Re: How can I select data to the end of a range in a macro?

Re: How can I select data to the end of a range in a macro?

Re: How can I select data to the end of a range in a macro?

Re: How can I select data to the end of a range in a macro?

Re: How can I select data to the end of a range in a macro?

Re: How can I select data to the end of a range in a macro?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro to select a date range within data with dates

[SOLVED] macro to select data range in rows correctly..

Macro to select data within a date range.

Macro to select cells at end of range of data

Macro to select only cells within the range that contain data

Bookmarks

Bookmarks

Posting Permissions