Delete the incorrect used range after the last row of data.

[frank_m]

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

[david000]

This bit of code would prevent any entry below the last row you would allow for data entry, because the user can't get below it, you don't need to hunt down any erroneous data for deletion. It's something to consider at least.


Dim ReturnCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Set Range1 = Target
Set Range2 = Cells(UsedRange.Rows.Count, UsedRange.Columns.Count)

If Range1.Row > Range2.Row + 1 Then
MsgBox "Too far"
ReturnCell.Select
Else
Set ReturnCell = Selection
End If
End Sub

[frank_m]

Hi David

Thanks for your code. - It is a nice way to return selections that are beyond the last row.

However when using your code Excel still remembers the last selection for manual scrolling purposes.
- With using the looping and deleting portion of my code, saving and reopening the workbook, repairs the used range.