Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

Note: At first I was in love with tables, but after further testing I found to issues not worth the benifits to me which are...

  • My "merge/unmerge" macro wouldn't work on a table, but worked fine on a range. 
  • I was having trouble with my tabelize macro with the `Range(TblName & "[#All]").Select` line and enableing filters is dead simple.
  • You can't add a table on a "query" range but you can filter it.

Table:

Public Sub DatatoTable(ws As Worksheet)
   
    ws.Activate
    
    Dim TblStyle As String
    TblStyle = "TableStyleMedium20"
    
    Dim TblName As String
    TblName = ws.Name
     
    Dim TblRng As Range
    Set TblRng = GetUsedRange(ws)
    
    Dim IsQuery As Boolean
    IsQuery = cell_has_query(TblRng)

    TblRng.Select
    Set lo = TblRng.ListObject
    If ws.AutoFilterMode = False Then
     If IsQuery = False Then
      If Not lo Is Nothing Then
         Debug.Print "Table Found"
    Else
     ws.ListObjects.Add(xlSrcRange, TblRng, , xlYes).Name = TblName
     Range(TblName & "[#All]").Select
     ws.ListObjects(TblName).TableStyle = TblStyle
      End If
     ElseIf ws.AutoFilterMode = False Then Selection.AutoFilter
     End If
    End If
End Sub
Public Function GetUsedRange(ws As Worksheet) As Range
 Dim lRow As Integer, lCol As Integer, fCol As Integer, fRow As Integer
 fCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlLeft).Column
 lCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
 fRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlDown).Row
 lRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 Set GetUsedRange = ws.Range(Cells(fRow, fCol), Cells(lRow, lCol))
End Function
Sub Test()
 
 Call DatatoTable(ActiveSheet)
 
End Sub

Does WS have Query?:

https://stackoverflow.com/a/40492129/5079799

Public Function cell_has_query(rng As Range) As Boolean

    If rng Is Nothing Then
       cell_has_query = False
       Exit Function
    End If

On Error GoTo ErrHandler
    If Not rng.QueryTable Is Nothing Then
        cell_has_query = True
    End If
    Exit Function

ErrHandler:
    If Err.Number = 1004 Then 'Application-Defined or Object-Defined Error - this throws if there is a querytable with no destination
        cell_has_query = False
    Else
        On Error GoTo 0
        Resume
    End If
End Function
Sub TestQuery()
 Dim rng As Range
 Set rng = ActiveSheet.UsedRange
 Dim HasQuery As Boolean
 HasQuery = cell_has_query(rng)
 Debug.Print HasQuery
End Sub


Filter:

Public Sub FilterWorksheet(ws As Worksheet)

  Dim ColumnNumber As Long
  ColumnNumber = Columns.Count

  Dim ColumnLetter As String
  ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)
 
  Columns("A:" & ColumnLetter).Select
  If ws.AutoFilterMode = False Then Selection.AutoFilter

End Sub
  • No labels