Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
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"
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
How about using this code?
Thomas
Dim loTable As ListObject
Set loTable = ActiveSheet.ListObjects.Add(xlSrcRange, [a1].CurrentRegion, , xlYes)
loTable.TableStyle = "TableStyleLight8"
Thomas
You need to reference the sheet where you are trying to get the last row and column, something like this:
jppinto
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
I know you believe the range selection is ok but ... row and columns for a range can be in error when the relevant row/column is limited in population ... using the usedrange can avoid the problem so try the following.
Chris
Chris
Dim LastRow As Long
Dim LastCol As Long
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).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"
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!
@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
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
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
Chris
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
Chris
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
Chris
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!
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!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial