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").
