Selecting the first blank cell in a range.

[kingconsto]

Hello,

I have a spreadsheet and I would like to search from N5 forward and select the first empty cell (which in this case is AG5 but changes every day i.e AH5,AI5) and copy the column data to the left of that empty cell (in this case (AF5-AF157) and paste it to a new worksheet. Column count and end(xltoright/left) is not working because there is data from N5-AF5, blank cells from AG5-AS5, data in AT5, blank cell, data in AV5-AW5. This is what I have now.

lastcolumn = Range("N5" & Columns.Count).End(xlToRight).Column
Range(Cells(5, lastcolumn), Cells(157, lastcolumn)).Select
Selection.Copy


Thanks for the help!

 

[Rick Rothstein]

I have a spreadsheet and I would like to search from N5 forward and select the first empty cell (which in this case is AG5 but changes every day i.e AH5,AI5) and copy the column data to the left of that empty cell (in this case (AF5-AF157) and paste it to a new worksheet. Column count and end(xltoright/left) is not working because there is data from N5-AF5, blank cells from AG5-AS5, data in AT5, blank cell, data in AV5-AW5. This is what I have now.

lastcolumn = Range("N5" & Columns.Count).End(xlToRight).Column
Range(Cells(5, lastcolumn), Cells(157, lastcolumn)).Select
Selection.Copy

If Columns.Count for your version of Excel is 16384, then Range("N5" & Columns.Count) becomes Range("N5" & 16384) which becomes Range("N516384") after the concatenation. As you can see, you do not really want to be concatenating Columns.Count onto N5... remove the part I highlighted in red and your search for the blank cell will start at cell N5 as intended.

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG19Apr26
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
lst = Range("n5").End(xlToRight).Column
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(5, lst), Cells(Rows.Count, lst).End(xlUp))
Rng.Copy Sheets("Sheet2").Range("A1")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[kingconsto]

Perfect. Thanks!

 

[MickG]

You're welcome