frank_m
12-24-2012, 04:24 PM
If I select a cell below the last data row, that cell becomes the bottom of the page when scrolling.
I wrote this code to erase the incorrect used range that might have been created below the last row of data. -- I do see that in some cases it may do an incomplete job, but I think it's good enough for my needs. -- I would though like opinions as to weather or not it could in any case put my data integrity at risk.
Thanks
Dim cl As Range
Dim cnt As Long
Dim LastRow As Long
Dim rng As Range
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
If ActiveCell.Row > LastRow Then
MsgBox "Selection beyond the last row of data is not permited"
With ActiveSheet
Set rng = .Range(.Cells(LastRow + 2, 1), .Cells(ActiveCell.Row, 32))
Application.EnableEvents = False
.Cells(LastRow, 7).Select 'select column 7 cell in last row of data
End With
ActiveWindow.ScrollRow = LastRow'scroll to last row
'if below last row, loop thru the range between the selected cell and the last row
'if all are empty, delete incorrectly registered used range below last row of data
For Each cl In rng
If Not cl.Value = "" Then
cnt = cnt + 1
End If
Next cl
If cnt = 0 Then
rng.Delete Shift:=xlUp
End If
Application.EnableEvents = True
End If
I wrote this code to erase the incorrect used range that might have been created below the last row of data. -- I do see that in some cases it may do an incomplete job, but I think it's good enough for my needs. -- I would though like opinions as to weather or not it could in any case put my data integrity at risk.
Thanks
Dim cl As Range
Dim cnt As Long
Dim LastRow As Long
Dim rng As Range
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
If ActiveCell.Row > LastRow Then
MsgBox "Selection beyond the last row of data is not permited"
With ActiveSheet
Set rng = .Range(.Cells(LastRow + 2, 1), .Cells(ActiveCell.Row, 32))
Application.EnableEvents = False
.Cells(LastRow, 7).Select 'select column 7 cell in last row of data
End With
ActiveWindow.ScrollRow = LastRow'scroll to last row
'if below last row, loop thru the range between the selected cell and the last row
'if all are empty, delete incorrectly registered used range below last row of data
For Each cl In rng
If Not cl.Value = "" Then
cnt = cnt + 1
End If
Next cl
If cnt = 0 Then
rng.Delete Shift:=xlUp
End If
Application.EnableEvents = True
End If