Select Range (Last Row to Last Column)

[BrittKnee]

Hi All! I am trying to insert a table to a selected range, but am running into an error. Below is the current code I am using:

Dim LRow As Long
Dim RngA As Range


'Create Table (Assignment_Data)

Application.ScreenUpdating = False

LRow = Cells(Rows.Count, 1).End(xlUp).Row
LCol = Cells(1, Columns.Count).End(xlToLeft).Column
RngA = Range(LRow, LCol).Select

 Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, RngA, , xlYes).Name = _
        "Table1"

Thanks in advance!

 

[6StringJazzer]

You have a number of problems.

First, whenever you have an issue with a VBA error, always give the error number, error message, and what line of code it occurs on.

You should also show all of your code. Problems do not always originate where we think they do.

This line of code has multiple problems:

RngA = Range(LRow, LCol).Select

You must use Set. You should not use Select. If you are specifying row and column you must use Cells instead of Range. Note that LRow and LCol will find the last used cell in column A and the last used cell in row 1. I would assume you do not want to overwrite existing data so I would add 1 to each of those for the table location. Replace with this:

Set RngA = Cells(LRow + 1, LCol + 1)

 

[BrittKnee]

Thanks for your reply, but I am still getting an error starting with Set LRow. I totally blanked on using set.

Dim LCol As Long
Dim LRow As Long
Dim RngA As Range

'Create Table (Assignment_Data)
Application.ScreenUpdating = False

Set LRow = Cells(Rows.Count, 1).End(xlUp).Row  (ERROR HERE)
Set LCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set RngA = Range(LRow, LCol)

 Application.CutCopyMode = False
 Application.CutCopyMode = False
 ActiveSheet.ListObjects.Add(xlSrcRange, RngA, , xlYes).Name = _
        "Table1"

 

[6StringJazzer]

I did not suggest you do that. I suggested changing only one line of code, and you changed two other ones in addition.

You must use Set when assigning a Range to a Range variable. The other two are Long variables and you cannot use Set for those.