Can't reset the last used row when Activesheet.UsedRange is only entire columns

[Johnny C]

Hi.
I've inherited a spreadsheet that is generated from MS Dynamics (I think). There's about 300 rows. There's a macro that needs to amend something, looping down the rows to the last used row takes forever. The Last used row is the bottom of the sheet (row 1048576

I tried the usual, deleting rows, goto A1, save. But that didn't work. I tried various suggestions but narrowed it down to the fast that the Activesheet.UsedRange property only refers to columns, $A:$J. I'm assuming that's because it's somehow imported, I've never seen a sheet before with no row in the UsedRange property.

You can't set the UsedRange property. I could get round it by working out where the data stopped, copying the data elsewhere, deleting the columns, copying the data back but that won't work if there's links to the original data, you'll end with a load of #REFs.

Is there a way I can fix it in situ?

I can't do a one off fix as there are hundreds of files in the same boat.

Cheers

 

[RoryA]

Sometimes changing the row heights of all the rows then resetting to whatever the sheet's standardheight is can fix things like that, assuming you've already sorted all other possibilities.

 

[Johnny C]

Thanks Rory, that fixed it. I knew that if anyone would know you would

I had read somewhere that it's because in the XML spreadsheet it gives a rowheigtht of 1, when delete it just repoulates with a row height of 1. Changing the rowheight, deleting puts rows back but with the revised height and that somehow resets the used range.

I will pop that into VBA so I can add it to the QAT.

Cheers!

 

[Johnny C]

This does the trick (for me anyway

Sub RestUsedRange()

Dim CurrentRowHeight&, YesNo&

CurrentRowHeight = ActiveSheet.UsedRange.RowHeight
ActiveSheet.UsedRange.RowHeight = 23
Selection.EntireRow.Select
Selection.EntireRow.Delete
ActiveSheet.UsedRange.RowHeight = CurrentRowHeight
YesNo = MsgBox("Save file to commit new Used Range?", vbYesNo)
If YesNo = vbYes Then
    ActiveWorkbook.Save
End If

End Sub

 

[RoryA]

FWIW, I like to use activesheet.standardheight to make sure they are set to the default.

 

[Johnny C]

FWIW, I like to use activesheet.standardheight to make sure they are set to the default.

Cheers, good shout.

 

[Johnny C]

Cheers, I'll use that. Good shout.