Avatar of snailcat
snailcat
Flag for United States of America asked on

Help with Excel Visual Basic code

Hi,

Can anybody figure out the problem with this code? I am trying to use table formatting on a worksheet.
The code should figure out the range of data and then format to TableStyleLight8.  When I debug with my file the range seems to be finding the last column (O) but the table format is being applied and missing the last column.

Thanks
Dim LastRow As Long
    Dim LastCol As Long
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = Cells(LastRow, Columns.Count).End(xlToLeft).Column
    Range("A1" & ":" & Cells(LastRow, LastCol).Address).Select
    


    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1" & ":" & Cells(LastRow, LastCol).Address), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight8"

Open in new window

Microsoft ExcelMicrosoft OfficeVB Script

Avatar of undefined
Last Comment
nutsch

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
nutsch

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jppinto

You need to reference the sheet where you are trying to get the last row and column, something like this:

jppinto
Dim ws as Worksheet
Dim lstRow as Long
Dim lstCol as Long

Set ws=Sheets("Sheet1")

lstRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
lstCol = ws.Cells(1, ws.columns.Count).End(xlToLeft).Column

Open in new window

SOLUTION
Chris Bottomley

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
snailcat

ASKER
Thanks for the rapid response!
Chris Bottomley

Glad it helped and thanks to you as well for the rapid response.

Chris
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
jppinto

snailcat: did you at least tryed my code? Basically does the same as Chris, pointing out that your last row and last column definition was not OK, the rest of the code he posted was the same that you had!
nutsch

@snailcat

I'll join the Joao choir on this one: Can you comment on why you picked Chris's (very valid) answer over the other two?

Thomas
Chris Bottomley

From my perspective folke you retained the:

ws.Cells(ws.Rows.Count, "A").End(xlUp).row syntax

this fails at times if the row or column being addressed does not have data to the end of the range.

What you seem to be missing about my post is:

ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row takes the last column or row of the used range i.e. last of all columns/rows rather than a defined row or column.

Chris
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
nutsch

Chris, I used currentRegion, not lastRow, because I expected the issue to be an empty last cell.

T
Chris Bottomley

Indeed thomas but current region still falls over if data is placed outside the A1 region such that there is a clear column / row between hence it is still the same limitation in principle.

Chris
snailcat

ASKER
Hi All--
Appreciate everybody's comments and assistance.

FYI my dataset has a blank cell in the first and last column of the last row of data which was why my code was failing.

I was viewing this on my phone and I missed @nutsch's code when it was posted, so I apologize in advance since that was the first solution.  I did test it afterwards and it works fine with my dataset.

@jppinto--your solution did not work--it found the last column but it missed the last row so that the formatted table range was short by 1 row.

At least in my dataset the solutions by @nutsch and @chris_bottomley worked equally well for my dataset.  I apologize for not splitting the points since both solutions took care of my problem.  I have been an expert in similar situations and hate when that happens.

THANKS again!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
nutsch

No issue for the points. The question was my for my learning process. Thanks for the explanation.

Thomas