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