Excel/Finding next empty column in a row
QUESTION: Hi Jerry,
I am writing a macro that places information obtained from the user into certain cells automatically. I want the macro to put the information that the user provides into the next empty column in a particular row. The spacing is such that there will be an empty column every so often, so I want the macro to automatically place the information into the column directly to the right of the very last column used in that particular row. I know how to do this for the first empty cell in a column, as shown by the code below:
Range("C" & Rows.Count).End(xlUp).Offset(1).Value = x
where x is the information from the user.
My question is, how do I rewrite this line of code so that it finds the first empty column in a particular row, instead of the first empty row in a particular column?
Thank you in advance for your help!
ANSWER: To put an "x" in the empty cell to the right of the last used cell in row 5:
Cells(5, Columns.Count).End(xlToLeft).Offset(,1).Value = "x"
To use your FIRST original code to find the next empty row to start using, then use that same row as you add more values from the user, you'd need to store the current row in a variable.
Dim CurrRow as Long
CurrRow = Range("C" & Rows.Count).End(xlUp).Row + 1
Cells(CurrRow, "C").Value = "x"
Cells(CurrRow, Columns.Count).End(xlToLeft).Offset(,1).Value = "y"
---------- FOLLOW-UP ----------
Great, thank you very much. That was helpful. I used the Cells(5... line of code with a For loop to get multiple inputs from the user and use the next blank column in a certain row.
I now realize that I need to find the first empty column in a range of rows (2-21). I can't use offsets because the position of the reference will vary with respect to the cell which contains the last entry in said range of rows. How do I write to a particular cell in a row that is 2 columns to the right of the column with the last entry in said range of rows?
Thanks again :-)
Struggling a little here to understand the problem. If you're saying that you need to:
1) Find the last used row, this line of code will store that last used row in a variable
LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastROW = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
2) To find the last used column regardless of which row that use occurred, you store the answer in a variable thusly:
LastCOL = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Now, you have the last used row and the last used column. You can now use the LastROW and LastCOL variables to insert a value where you'd like in relation to those values by simply adding to the result.
To add a value 2 columns to right of the last used column, but in the same row as the last used row:
Cells(LastROW, LastCOL + 2).Value = "y"
You can play with that to get pretty much any positioning you'd like.