Find last filled cell in column, simple dialog box

Re: Find last filled cell in column

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?

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

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

Re: Find last filled cell in multiple columns?

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?

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?

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

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