Finding the last item in a group of ranges

[jonco]

I have some rows of data on a spreadsheet that are in groups of 4 fields
(columns) and have 5 sets across the screen so they're all visible at once.
Fileds are Amt, Date, # and Note. These "records" are each transactions
and they are in cells A531. The next sets are in E5:H31, I5:L31, M531
and Q5:531.

What I want to do is to find the last item in these rows, or the last
transaction.

This is the code I've used to find the first empty row in the cells:

Sub FirstEmpty() ' Finds first empty row on customer's sheet (card)
Set Srng = ActiveSheet.Range("A4:A31,E5:E31,I5:I31,M5:M31,Q5:Q31")
Set FoundCell = Srng.Find(what:="", LookIn:=xlFormulas,
searchorder:=xlByRows)
If Not FoundCell Is Nothing Then
FoundCell.Select ' Select empty cell
Exit Sub

I thought of using that and then just using an Offset line to go back one
line...but that won't work if I happen to be on the first line of a new
column.

Any help would be appreciated

Jonco

 

[Gary Keramidas]

maybe something like this, if all columns end in the same row

Sub test()
Dim lrow As Long
Dim Srng As Range
lrow = Range("a5000").End(xlUp).Row + 1

Set Srng = ActiveSheet.Range("A4:A" & lrow & ", E5:E" & lrow & ", I5:I" & lrow _
& ",M5:M" & lrow & ", Q5:Q" & lrow)

End Sub

 

[Guest]

Depends how you fill things in. Do you do it by column or by row. That is to
say do you do all of A then E:H or do you do all of row 5 then all of row 6
then... From your descrition it sounds like by column. The answer will be
heavily dependant on that however. Your find code is not bad but you really
want to look Backwards from the first record until you find a populated
Cell...

Sub FirstEmpty() ' Finds first empty row on customer's sheet (card)
Set Srng = ActiveSheet.Range("A4:A31,E5:E31,I5:I31,M5:M31,Q5:Q31")
Set FoundCell = Srng.Find(what:="*", LookIn:=xlFormulas,
searchorder:=xlByRows, SearchDirection:=xlPrevious)
If FoundCell Is Nothing Then
ActiveSheet.Range("A4").select
Else
if FoundCell.row = 31
foundcell.select
msgbox "startnewcolumn by offsetting from here"
else
foundcell.offset(0,1).select
Exit Sub

It should be something like that...

 

[jonco]

Thanks guys. I got it working. You guys are great!

Jonco