Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

VBA for getting to a empty column

0

Morning

I just began using VBA for work and building macros - wow, love it. 

Using youtube a ton and learning. 

Total newbie question here. 

I have a data set I get monthly, trying to setup a macro to run the data set.

Trouble I am having is getting to the "cell" I want it start to run in. 

As in, every month there is more data and the columns are now +1 from prior month.

I am using a code (simple) to get to the next empty cell - but I cannot get the code to now find the next empty column.

 Range("A1").End(xlDown).Offset(1).Select 

What do I need to add to find the empty column next?

Thanks

Mark

Answer
Discuss
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(35% Sale Ends Jan. 26)

Answers

0

Either try going 1 column to the right of CurrentRegion  n=range("A1").currentregion.columns.count +1  OR start way out to the right (start in Column ZA) and do the EndLeft with offset + 1 column

Discuss

Discussion

Morning ~
I can see how to get to the first column - but I need to get to the first column after I get to the first row. 

As in, down first to empty column, then over to the right.
I know I am close and been monkeying around with this for a few hours now.

Example:
Need to get to Cell F7 from A1. 
I can get to ROW 7, but then how do I get to Cell F?
The problem is the data table changes monthly, sometimes it is F7, next time its G8.

Mark

Mark_weir Oct 20, '21 at 11:32 am
Add to Discussion
0

Mark

To get the last column for row 1 (say), paste this in the Intermediate Window of VB Explorer then press return to get the column number:

Debug.Print Cells(1, Columns.Count).End(xlToLeft).Column
(the bit in bold returns that number). Change the (italic) 1 to 3 if you want to do that for row 3 say.

Form your discussion point above, sounds like you want to select a cell just beyond any column and row headers (in row 1 and column A). To do so, use this (and declare the last row LstRw and column variables as Integers or Double if you like):

LstRw=Cells(Rows.Count, 1).End(xlUp).Row

LstCl=Cells(1, Columns.Count).End(xlToLeft).Column

Cells(LstRw,LstCl).Offset(1,1).Select
Note that the LstRw code is an alternative to yours and to be found in Don's tutorial: Get the Last Row using VBA in Excel (and there's one there  for last column number too)
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login