Find Empty Cell in Range From Bottom Up

[zoom38]

Good evening,

I have a chart on a spreadsheet in which I have 5 columns (G17:G42), (I17:I42), (K17:K41), (M17:M42), (O17:O42) that I want to populate. I am able to populate them from top to bottom, over to the right, top to bottom, over to the right, top to bottom and so on. What I am trying to do is now is populate the chart from bottom to top, over to the left, bottom to top, over to the left and so on.

This is a snippet of the code I am using to do it from top to bottom (first column) which works

For Each m In Range("$G$17:$G$42")
LastEntry = "Empty"
If m.Value = "" Then
Exit For
Else
LastEntry = "Full"
End If
Next m

I cannot figure out how to modify it to go bottom to top. I've tried to modify it multiple ways but can't figure it out.

Attached is a shortened spreadsheet with what I'm doing. Sheet 1 is the bottom to top routine I can't figure out. Sheet 2 is the top to bottom routine that works.

Thank you in advance.
Gary

[georgiboy]

You will need to make some backwards loops and call the cell through the use of 'Cell' and not 'Range', see below to loop through your range backwards. (P.S. Do not use merged cells unless your life depends on it)


Sub test()
Dim x As Long, y As Long

For y = 15 To 7 Step -2
For x = 42 To 17 Step -1
Cells(x, y).Select
Next x
Next y
End Sub

You could use something like the above to create a function to write to the range backwards, you could then just call the function each time you wish to write to your backwards tables. Function example below:

Sub test()
RevCell 42.6666
End Sub


Function RevCell(str)
Dim x As Long, y As Long

For y = 15 To 7 Step -2
For x = 42 To 17 Step -1
If Cells(x, y) = vbNullString Then
Cells(x, y) = str
Exit Function
End If
Next x
Next y
End Function

Hope this helps

[zoom38]

@ georgiboy,

I modified your function to my needs and it works perfectly.

Thank you very much.

[Aussiebear]

(P.S. Do not use merged cells unless your life depends on it)

This is a function (Merged Cells) within excel which needs to be removed. It causes more problems than it solves.