select next empty cell
Posted by chris on March 27, 2001 2:33 PM
I am trying to select the next empty cell after a simple sum function is performed and then continue on with some delete functions I have. You can follow along in the code to see exactly where I am off a bit.
If you know the missing link please help. Thanks
Option Explicit
Sub FindLastRow()
Dim LastRow As Integer
With ActiveSheet
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
End With
Dim s As Worksheet
Set s = ThisWorkbook.ActiveSheet
' Perform this sum function in A12
s.Cells(LastRow + 1, 1) = "=sum(a1:a" & LastRow & ")"
'***
'*****
' This is my guess at the solution. I'm off a bit here.
s.Range(LastRow + 2, 1).Select
'*****
'***
' Delete all cells from A13 over and down.
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Set s = Nothing
End Sub