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

Posted by David Hawley on March 27, 2001 3:16 PM

**


Hi Chris

Try this:

Sub TyThis()
Dim SumCell As Range
Set SumCell = Range("A65536").End(xlUp).Offset(2, 0)
SumCell = "=Sum(A1:" & SumCell.Offset(-2, 0).Address & ")"
End Sub

OzGrid Business Applications



Posted by chris on March 28, 2001 7:36 AM

**

Does not work because I want to delete ALL cells after my last row with populated cells. I don't want to delete something I just put on the sheet.
something like this:
100
100
sum(a1:a2) 200
DELETE ALL CELLS IN THIS ROW AND BELOW.