I want to get the number of rows and columns in my "selection" range, but for some reason Columns.Count returns 1 when I have multiple columns selected.
Anyone knows why?
I am using excel 2013 32 bit.
Anyone knows why?
I am using excel 2013 32 bit.
Code:
Sub test()
Dim sourceSht As Worksheet
Dim targetSht As Worksheet
Dim Table As ListObject
Dim LastRow As Long
Dim selection As Range
Set sourceSht = ActiveWorkbook.Sheets("Colaboradores")
LastRow = sourceSht.ListObjects("Table3").Range.Columns("BB").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set targetSht = ActiveWorkbook.Sheets("Sheet3")
'Check for existance of table
'SELECT ALL NEEDED DATA
Set selection = sourceSht.Range("Table3[UE i" & vbLf & "(área)], Table3[UE ii" & vbLf & "(unidade)],Table3[2013 -1ºSemestre]:Table3[2019-2ºSemestre]").Offset(-1, 0)
Debug.Print selection.Columns.Count <<<<<<<< Here the console returns 1 ( also the number of rows does not change with or without the offset, which is weird to me too)
'COPY AND PASTE INTO NEW SHEET
selection.Copy
targetSht.Range("A1").PasteSpecial xlPasteValues
'CREATE NEW TABLE
Debug.Print h
Set selection = targetSht.Range("A1").Resize(w, h)
w = selection.Rows.Count
h = selection.Columns.Count
Debug.Print h
targetSht.ListObjects.Add(xlSrcRange, selection, , xlYes).Name = "MyTable"
End Sub
Last edited by a moderator: