I need a simple macro to, starting at the right edge of a single row range ( B2:F2), search leftward cell by cell to find the first non-blank cell,

[TFStacy]

I am a beginner learning by doing / learning by web search. My current need has a lot of near misses posted but I can't find the solution I need. I will try to be specific:

I need a simple macro to, starting at the right edge of a single row range ( B2:F2) that is defined in the solution you post, search leftward cell by cell until a non-blank cell is encountered, then select the cell to the right of it.

If the rightmost cell in the range is non-blank, returns an error message

If no non-blank cells are encountered in the range, select the leftmost cell in the range

stop




Thank you!

 

[Trevor_S]

Try:

Sub MacroName()
If Range("F2").Value = "" Then
  Range("F2").End(xlToLeft).Offset(0,1).Select
  If Selection.Column < Range("B2").Column Then Range("B2").Select
Else
  MsgBox("Your error message here")
End If

Its not the shortest code to achieve it, but hopefully should be easy to adapt. Change the B2 and F2 references as needed, and amend the error message to whatever you want.

 

[TFStacy]

Try:

Sub MacroName()
If Range("F2").Value = "" Then
  Range("F2").End(xlToLeft).Offset(0,1).Select
  If Selection.Column < Range("B2").Column Then Range("B2").Select
Else
  MsgBox("Your error message here")
End If

Its not the shortest code to achieve it, but hopefully should be easy to adapt. Change the B2 and F2 references as needed, and amend the error message to whatever you want.

Thanks. I inserted this into my macro and when I ran it it told me it could not paste myclipboard because the sizes of the selections were not identical. There are no merged cells. I had cells BG3:BG102 on the clipboard and they should have pasted in the column your code selected starting with the cell your code had selected. I offer two screenshots here. Thank you!

-- removed inline image ---



-- removed inline image ---

 

[Trevor_S]

Unfortunately I can't see the images. However I've added a line to the code to make the selection the same shape (1 column, 100 rows). If this doesn't work, could you please paste your full code here, so that I can see how my part fits in?

Sub MacroName()
If Range("F2").Value = "" Then
  Range("F2").End(xlToLeft).Offset(0,1).Select
  If Selection.Column < Range("B2").Column Then Range("B2").Select
Range(Selection, Selection.Offset(99,0)).Select
Else
  MsgBox("Your error message here")
End If