Unable to get the specialcells property of the range class

Isaac21

New Member
Joined
Jan 10, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hello all, the following code is for a uni assessment. The error "Unable to get the specialcells property of the range class" appears when i attempt to run the code, when i debug the code the debugger flags the line
"lastRow = wsSrc.Range("A:A").SpecialCells(x1CellTypeLastCell).Row" I'm attempting to find the last row on a worksheet using this code

Can any one help me solve this issue,
Thanks
Isaac

VBA Code:
Sub CombineWorksheets()
'(Header comments)
Dim wsSrc As Worksheet
Dim wsDest As Worksheet
Dim rngDest As Range
Dim lastRow As Long
Dim destRow As Long
Set wsDest = Worksheets("Combined")
Set rngDest = wsDest.Range("A2")
Application.DisplayAlerts = False 'supress prompt for worksheet deletes
 
'loop through all source sheets in this workbook
For Each wsSrc In ThisWorkbook.Sheets
    If wsSrc.Name <> "Combined" And wsSrc.Name <> "summary" Then 'all sheets except Combined
       lastRow = wsSrc.Range("A:A").SpecialCells(x1CellTypeLastCell).Row
       wsSrc.Range("Al", wsSrc.Range("I" & lastRow)).Copy Destination:=rngDest
       Set rngDest = rngDest.Offset(lastRow - 1) 'update the destination range
       wsSrc.Delete 'delete the source worksheet without a prompt
     End If
Next
Application.DisplayAlerts = True 'turn prompts back on
End Sub
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Isaac21

New Member
Joined
Jan 10, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Should be xl not x1.
Thank you so much i cant believe I made such a simple mistake, I have a lot to learn, the next line of code is giving me the error Method 'Range' of object'_Worksheet failed, would you know why that is? Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
You've got an L not a 1 in the wsSrc.Range("Al",
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
i cant believe I made such a simple mistake
We have all made them at some point, such mistakes are often the hardest to find.

If you had used Option Explicit at the top of your code module then it would have raised the first mistake as an undeclared variable which might have been easier to identify, but I don't know of any way to error check the typo that @Fluff has picked up prior to execution.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,677
Members
415,921
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top