Count number of rows and columns in range

[gui132]

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.

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

 

[Joe4]

Welcome to the Board!

Don't use reserved words like "selection" or "Table" as the name of variables! It can cause ambiguity, confusion, unexpected results and errors. When you refer to "selection", VBA cannot be sure if you are talking about your variable, or the default range "Selection".

Try changing those references, and see of that makes any difference.
You can also confirm that your range selection contains the range you think it should (to ensure that the range you are selecting really does contain more than one column) with a command like this:

Dim rng as Range
Set rng = ...
[COLOR=#ff0000]MsgBox rng.Address[/COLOR]

 

[gui132]

Welcome to the Board!

Don't use reserved words like "selection" or "Table" as the name of variables! It can cause ambiguity, confusion, unexpected results and errors. When you refer to "selection", VBA cannot be sure if you are talking about your variable, or the default range "Selection".

Try changing those references, and see of that makes any difference.
You can also confirm that your range selection contains the range you think it should (to ensure that the range you are selecting really does contain more than one column) with a command like this:

Dim rng as Range
Set rng = ...
[COLOR=#ff0000]MsgBox rng.Address[/COLOR]

Thank you for your answer! I will take your advice, but I actually figured out what was wrong. "Columns.Count" only returns the number of columns of the first area inside of the range, in my case I have 3 areas the first two are separate columns and the last is the sequence of columns. Because Columns count only returns the count for the first area it correctly returns 1. The solution is to loop through all areas and add the number of columns per area.

 

[Joe4]

I am glad you figured it out. I was hoping that the "rng.Address" would expose what range you are looking at and clarify what is actually happening.
I use MsgBox's a lot to confirm that what I think should be happening actually is happening.