Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



Find last filled cell in column, simple dialog box

Posted by Ben on August 05, 2000 9:08 PM
Hello. I just need help with a couple of simple items.
First, how do I Loop a macro so that it continues until it reaches the last filled cell or the end of the spreadsheet in a column of non-contiguous data?
Second, how do I get a simple dialog box to pop up when the macro is running to alert the user that one of the cells was filled in incorrectly?
Thank you.

Check out our Excel VBA Resources

Re: Find last filled cell in column

Posted by Ben on August 06, 0100 12:50 PM
Thanks Celia. The message box was incredibly easy but I still can't get my macro to work on the filled-in cells in a column. I've never used Dim or other declarations. I always use Do Until...Loop. I can select the proper range or I can let the macro run to infinity but I can't combine them. The best I have:
Sub TilEmpty_e()
Dim cell As Range
Set Range1 = Range(Range("G4"), Range("G65536").End(xlUp))
Range1.Select
For Each cell In Range1
If ActiveCell = "OFFICE" Then
ActiveCell.Offset(0, 1).Value = 1
ElseIf ActiveCell = "OFFICE-DR" Then
ActiveCell.Offset(0, 1).Value = 4
Else: ActiveCell.Offset(0, 1).Value = 1
ActiveCell.Formula = "UNKNOWN!"
MsgBox "Location is not filled in!"
End If
ActiveCell.Offset(2, 0).Select
Next
End Sub



Re: Find last filled cell in multiple columns?

Posted by Celia on August 08, 0100 3:39 PM
Ben
Unless there are used cells in columns other than A:I that are lower than the last used cell in columns A:I (or unless I’m misunderstanding), the code I sent should do what you want :-
Set range1 = Intersect(ActiveSheet.UsedRange, Range("G4:G65536"))

Just set a different variable for each column.

If you just want to locate the last used cell in columns A:I :-
Range("A:I").SpecialCells(xlLastCell).Select

Or if you want to store the row number of the last used cell in columns A:I :-
Dim lastRow as integer
lastRow= Range("A:I").SpecialCells(xlLastCell).Row

Et cetera………
Celia

, I'm trying to find the last-filled cell among the columns A:I for my macro TilEmpty, each of which ends at a different row. I want to do a special fill down that goes to, but not past, the lowest filled cell on the spreadsheet. I can find the last-filled cell in a single column. How do I do it for multiple columns? : Ben


Re: Find last filled cell in column

Posted by Celia on August 06, 0100 5:25 PM
Ben
Try this :-

Sub TilEmpty_e()
Dim Range1 As Range, C As Integer, N As Integer
Set Range1 = Range(Range("G4"), Range("G65536").End(xlUp))
C = Range1.Cells.Count
Range1(1, 1).Select
For N = 1 To C Step 2
If ActiveCell = "OFFICE" Then
ActiveCell.Offset(0, 1).Value = 1
ElseIf ActiveCell = "OFFICE-DR" Then
ActiveCell.Offset(0, 1).Value = 4
Else: ActiveCell.Offset(0, 1).Value = 1
ActiveCell.Formula = "UNKNOWN!"
MsgBox "Location is not filled in!"
End If
ActiveCell.Offset(2, 0).Select
Next
End Sub Thanks Celia. The message box was incredibly easy but I still can't get my macro to work on the filled-in cells in a column. I've never used Dim or other declarations. I always use Do Until...Loop. I can select the proper range or I can let the macro run to infinity but I can't combine them. The best I have:



Re: Find last filled cell in column

Posted by byates@jps.net on August 06, 0100 8:39 PM



Re: Find last filled cell in multiple columns?

Posted by Ben on August 06, 0100 8:46 PM
Thanks. It works. I also discovered a way to do it:
Sub TilEmpty_e()
Range("G4").Select
Set EmptyCell = Cells(160, ActiveCell.Column)
Set BottomCell = EmptyCell.End(xlUp)
Do Until ActiveCell.Row = BottomCell.Row
If ActiveCell = "OFFICE" Then
ActiveCell.Offset(0, 1).Value = 1
ElseIf ActiveCell = "OFFICE-DR" Then
ActiveCell.Offset(0, 1).Value = 4
Else: ActiveCell.Offset(0, 1).Value = 1
ActiveCell.Formula = "UNKNOWN!"
MsgBox "Location is not filled in!"
End If
ActiveCell.Offset(2, 0).Select
Loop
End Sub
But now I'm trying to find the last cell in the longest column from A:I. Any thoughts? Ben


Re: Find last filled cell in multiple columns?

Posted by Ben on August 07, 0100 10:06 PM
Celia, I'm trying to find the last-filled cell among the columns A:I for my macro TilEmpty, each of which ends at a different row. I want to do a special fill down that goes to, but not past, the lowest filled cell on the spreadsheet. I can find the last-filled cell in a single column. How do I do it for multiple columns?
Ben Ben


Re: Find last filled cell in multiple columns?

Posted by Celia on August 06, 0100 10:22 PM

Ben
I presume that what you are looking for is the last used cell on the worksheet (or the last used row ).
There are a number of ways of doing this - it depends what you need it for.
For example, in your macro TilEmpty_e, if you want to set the range in Column G from G4 to the last used row on the worksheet, instead of to the last used cell in Column G :-

Set range1 = Intersect(ActiveSheet.UsedRange, Range("G4:G65536"))

With this code, for the range to start always at G4, there has to be at least one cell with data in rows 1:4 (otherwise the range will start at the first row with data below row 4).

If this does not provide what you were looking for, post again with more specific details.

Celia



Re: Find last filled cell in column, simple dialog box

Posted by Celia on August 05, 0100 11:46 PM


Ben
Select the range of data in the column or declare it as a variable and then to loop thru it :-

Dim cell as range
For each cell in YourRange
'put you code here for what you want to do to each cell
Next

To identify or select the range of data in column A from row 4 (for example) to the last filled cell :-
Range(Range("A4"),Range("A65536").End(xlUp))

To show a message box if something’s wrong :-

If Range("A1")<5 then
MsgBox "Cell A1 is less than 5"
End
End If

When the user clicks OK, the macro will be exited and the rest of the macro will not be run.
If you want it to continue after the user clicks OK :-

If Range("A1")<5 then
MsgBox "Cell A1 is less than 5"
End If

Celia





This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.