PDA

View Full Version : Do tables work with well macros.



simmalbert
09-17-2016, 02:50 AM
Hi Folks,

This is a very general question.I converted my spreadsheet of data to a table.Once converted to a table each column heading has a small down arrow that enables further filtering.I can then run various formulas on the data or filtered data.
Eveything works OK,and it's a really great tool.The problem comes when I try to automate it with a macro.The results are mixed,sometimes no problems at all,other times inconsistencies and on other occasions the completely wrong result,sometimes it can also be very slow.
I have now been told that using Tables with macros is not a very good idea and can lead to all sort of problems and is best avoided altogether.Is this true,is there an alternative?
Any help appreciated on this.

Aussiebear
09-17-2016, 03:48 AM
My experience suggests that naming ranges as tables is a good thing. Using named ranges/tables within macros is brilliant.

simmalbert
09-17-2016, 04:29 AM
Hi Aussiebear,
Thanks for the reply.So are you suggesting that I name the data sheet first(give it a range name rather than just say Sheet1) then turn it into a table?
Kind Regards
Arthur

SamT
09-17-2016, 09:07 AM
Name the Table (Range). If the Table is static.

For dynamic tables you can use a Custom Sheet Property or a Sheet Public Function

The inner code for both Property and Function is the same


Property Get NameOfTable() As Range
Dim LastCol As Long
Dim LastRow As Long
LastCol = Cells(1, Columns.Count).End(xlToRight).Column
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

NameOfTable = Range(Range("A1"), Cells(LastRow, LastCol))
End Property


Public Function GetTable() As Range
Dim LastCol As Long
Dim LastRow As Long
LastCol = Cells(1, Columns.Count).End(xlToRight).Column
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

GetTable = Range(Range("A1"), Cells(LastRow, LastCol))
End Function

They are even used the same way

Other Sub Code

Dim myTableRange As Range
myTableRange = Sheets("Sheet1").NameOfTable
'OR
myTableRange = Sheet1.GetTable

simmalbert
09-17-2016, 12:37 PM
Hi Samt>
thanks for that,great code I had never thought of naming the range.Thanks
Kind Regards
albert simm