Quote:
|
I've been trying to figure out what's different, same columns and headers and similar data. Only thing I noticed is my dataset in excel seems to be longer than it is, meaning my scroll bar thinks there's data in about 1000 cells below my actual data. I've tried clearing them and see no values or formulas. But when I Shift-Control-End it includes a bunch of blank cells. Do you think that could be causing the issue?
|
Didn't noticed the size of the used range before.
Run this macro against the sheet and see if it rectifies things.
If it doesn't, you'll need to attach another Example data sheet that has these issues.
Code:
'CREDIT: MARK858
'https://www.mrexcel.com/forum/excel-questions/1073109-usedrange-no-longer-working.html#post5155296
Sub LoseThatWeightx()
Dim xx As Long, LastRow As Long, LastCol As Long
Application.ScreenUpdating = False
With ActiveSheet
LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
.Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
.Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
End With
xx = Application.ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = True
End Sub