You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Finding next empty column in a row

Advertisement

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"

Etc...

---------- FOLLOW-UP ----------

QUESTION: Jerry,

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

Or

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.

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files
=====================
I have been offering free assistance as an Excel aid on many web sites for many years:
(http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)**Education/Credentials**

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition**Awards and Honors**

Microsoft Excel MVP 2010