Help with this code

G

Guest

Guest
Hi,

For the life of me I can't get this code to work. I have a lot of workbooks with worksheets where the usedrange of each sheet is something like A1:U23233 but there are only about 200 rows and 10 columns of data on each sheet. I have managed to set up this code which highlights all shades the real unused range in red (the final macro will delete the columns/rows and then save the file).

The error occurs just after the comment Delete the unused columns with the error message Method Range of object _Worksheet failed.

Any ideas?

Sub SortAndSave()
Dim sht As Worksheet, lngLastRow As Long, rngeFind As Range, lngLastCol As Long
Dim rngeTemp As Range

Set TheWorkbook = ActiveWorkbook
For Each sht In TheWorkbook.Worksheets
Set rngeFind = sht.Range(sht.UsedRange.Address).Find("*", , , , xlByRows, xlPrevious)
lngLastRow = rngeFind.Row
Set rngeFind = sht.Range(sht.UsedRange.Address).Find("*", , , , xlByColumns, xlPrevious)
lngLastCol = rngeFind.Column + 1

'Delete the unused columns
sht.Range(Cells(1, lngLastCol), Cells(1, 256)).Interior.ColorIndex = 3

'Now delete the unused rows
sht.Range(Cells(lngLastRow, 1), Cells(65535 - lngLastRow, 1)).Interior.ColorIndex = 3 'EntireRow.Delete
Next

'The workbook has been sorted, now save it and close it

'TheWorkbook.Close True

End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Don't worry I got it sorted.

There were some blank sheets in the workbook and so rngeFind was Nothing. I don't know why the debugger was highlighting this line:-
sht.Range(Cells(1, lngLastCol), Cells(1, 256)).EntireColumn.Delete

but this code seems to work:-

Sub SortAndSave()
Dim sht As Worksheet, lngLastRow As Long, rngeFind As Range, lngLastCol As Long

Set TheWorkbook = ActiveWorkbook
For Each sht In TheWorkbook.Worksheets
Set rngeFind = sht.Range(sht.UsedRange.Address).Find("*", , , , xlByRows, xlPrevious)

If Not rngeFind Is Nothing Then

lngLastRow = rngeFind.Row + 1

Set rngeFind = sht.Range(sht.UsedRange.Address).Find("*", , , , xlByColumns, xlPrevious)
lngLastCol = rngeFind.Column + 1

'Delete the unused columns
sht.Range(Cells(1, lngLastCol), Cells(1, 256)).EntireColumn.Delete

'Now delete the unused rows
sht.Range(Cells(lngLastRow, 1), Cells(65536, 1)).EntireRow.Delete
End If
Next

'The workbook has been sorted, now save it and close it

'TheWorkbook.Close True

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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
Back
Top