MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Celia -- Last filled row


Posted by Ben on August 09, 2000 7:10 PM

OK, so here's a clunky macro that accomplishes my objective:

Cells(160, 2).Select
Do While (ActiveCell = Empty And ActiveCell.Offset(0, 1) = Empty) = True
ActiveCell.Offset(-1, 0).Select
Loop
Set BottomCell = ActiveCell

And then I fill down to BottomCell.row with a column of data. It works fine for a spreadsheet of only 160 rows and two columns. Is there a more universal macro? By the way, xlLastCell doesn't work for me. It selects row 167 and column K (I have formatted cells to row 160, column J, and data usually only to about row 100.)


Posted by Celia on August 11, 0100 1:53 AM


Ben
Try this :-

Range("A:I").SpecialCells(xlLastCell).EntireRow.Select
Do Until Application.CountA(Selection) <> 0
Selection.Offset(-1, 0).Select
Loop
Selection(1, 256).End(xlToLeft).Select
Set BottomCell = Selection

Celia


Posted by Celia on August 11, 0100 2:00 AM

Correction


Sorry, to make sure the last cell only in columns A:I of the last row is selected, the code should read :-

Range("A:I").SpecialCells(xlLastCell).EntireRow.Select
Do Until Application.CountA(Selection) <> 0
Selection.Offset(-1, 0).Select
Loop
Selection(1, 10).End(xlToLeft).Select
Set BottomCell = Selection

Celia


Posted by Celia on August 11, 0100 2:16 AM

Re: Correction


Sorry. I might be in danger of getting it right in a minute :-

Range("A:I").SpecialCells(xlLastCell).EntireRow.Select
Do Until Application.CountA(Selection) <> 0
Selection.Offset(-1, 0).Select
Loop
Selection(1, 9).Select
If Selection = "" Then
Do Until Selection <> ""
Selection.Offset(0, -1).Select
Loop
End If
Set BottomCell = Selection

Celia

Posted by Ben on August 13, 0100 10:54 AM

Re: Correction

Range("A:I").SpecialCells(xlLastCell).EntireRow.Select


Celia, thanks, works great after a slight modification:

ActiveCell.SpecialCells(xlLastCell).EntireRow.Select
Do Until Application.CountA(ActiveCell.Range("A1:I1")) <> 0
ActiveCell.Offset(-1, 0).Select
Loop
Selection(1, 9).Select
If Selection = "" Then
Do Until Selection <> ""
Selection.Offset(0, -1).Select
Loop
End If
Set BottomCell = Selection

Range("A:I").SpecialCells(xlLastCell).EntireRow.Select
would not select the Range("A:I").