Application-Defined or Object-Defined Error on Range Resize

[gui132]

When i try to resize a range after offsetting it I get an Application-Defined or Object-Defined Error.
Can't understand why.

Sub test()




Dim sourceSht As Worksheet
Dim targetSht As Worksheet
Dim LastRow As Long
Dim selectedData 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 selectedData = sourceSht.Range("Table3[UE i" & vbLf & "(área)], Table3[UE ii" & vbLf & "(unidade)],Table3[2013 -1ºSemestre]:Table3[2019-2ºSemestre]").Offset(-1, 0)


h = selectedData.Rows.Count + 1 <<<< Because of the offset add one row to the range

Areas = selectedData.Areas.Count
w = 0
For i = 1 To Areas
w = w + selectedData.Areas(i).Columns.Count
Next i




Set selectedData = selectedData.Resize(h, w) <<<<<<<<<<<< Error occurs here


'COPY AND PASTE INTO NEW SHEET
selectedData.Copy
targetSht.Range("A1").PasteSpecial xlPasteValues


'CREATE NEW TABLE


Set selectedData = targetSht.Range("A1").Resize(h, w)  <<<<<<<<< This works fine
targetSht.ListObjects.Add(xlSrcRange, selection, , xlYes).Name = "MyTable"




End Sub

 

[gui132]

When i try to resize a range after offsetting it I get an Application-Defined or Object-Defined Error.
Can't understand why.

Sub test()




Dim sourceSht As Worksheet
Dim targetSht As Worksheet
Dim LastRow As Long
Dim selectedData 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 selectedData = sourceSht.Range("Table3[UE i" & vbLf & "(área)], Table3[UE ii" & vbLf & "(unidade)],Table3[2013 -1ºSemestre]:Table3[2019-2ºSemestre]").Offset(-1, 0)


h = selectedData.Rows.Count + 1 <<<< Because of the offset add one row to the range

Areas = selectedData.Areas.Count
w = 0
For i = 1 To Areas
w = w + selectedData.Areas(i).Columns.Count
Next i




Set selectedData = selectedData.Resize(h, w) <<<<<<<<<<<< Error occurs here


'COPY AND PASTE INTO NEW SHEET
selectedData.Copy
targetSht.Range("A1").PasteSpecial xlPasteValues


'CREATE NEW TABLE


Set selectedData = targetSht.Range("A1").Resize(h, w)  <<<<<<<<< This works fine
targetSht.ListObjects.Add(xlSrcRange, selection, , xlYes).Name = "MyTable"




End Sub

Fixed it. Resize does not work when you have multiple areas. Had to resize each area individually and join them with UNION.