Help with Last Column

Adam88

New Member
Joined
Dec 12, 2017
Messages
16
Hi,

I have a form used for data entry, I would like to save the data from one of the text fields into Row 2 of the first empty column on another worksheet. I've got a handle of how to do this for a row, but I can't quite workout how to change it to suit a column.

Hope you can help, thanks
Adam
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,097
This code will give you the column number of the first empty column in row 2 of "Sheet2".
Code:
Dim lCol As Long
lCol = Sheets("Sheet2").Cells(2, Columns.Count).End(xlToLeft).Column + 1
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

I use that method mumps posted all the time. However, the one caveat with it is if the entire row is blank to start, it will choose the second cell, not the first. I always had to add some other lines of code to accommodate that.
Then last week, someone showed me this really cool method of doing it, like this:
Code:
    Dim Rng As Range
    
    Set Rng = Rows(2).SpecialCells(xlBlanks)(1)
    Rng.Value = "Place value here"
It will always find the first blank, even if it is in the first column.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,097
@Joe4: Thank you for that little piece of code. Would it be a problem if row 2 is not entirely blank? For example, in row 2, there is data in all columns from A to Z except that column Y is blank.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

thank you for that little piece of code. Would it be a problem if row 2 is not entirely blank? For example, in row 2, there is data in all columns from a to z except that column y is blank.
It will actually find the first blank cell anywhere in that range (so, in the case you presented, it would find column Y).
If you wanted to find the first blank cell AFTER the last populated cell, then you would use the method you've described.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are welcome!

You know, it is amazing. I have been on this board for over 15 years and just learned that trick last week (just another reason why I love this board!).
 

Adam88

New Member
Joined
Dec 12, 2017
Messages
16
Thank you for your responses guys, I appreciate the help.

My knowledge on VBA is very limited in scope and I'm attempting to "Learn as I go!", if you could see the amount of books & PDF's I have around me, you'd probably say it's overkill!

I've taken what you've said on board although I'm still unsure of the best approach. In using mumps suggestion it returns the correct column, however I wish to then apply text (taken from a textbox.text on a userform) to the 2nd row. I've tried a few things following your suggestions but get various error messages related to object not defined.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,825
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Possibly...
Code:
Dim lCol As Long
lCol = Sheets("Sheet2").Cells(2, Columns.Count).End(xlToLeft).Column + 1
Cells(2, lCol).Value = UserForm1.TextBox1.Text

Else post the code that you are using that has failed.
 
Last edited:

Adam88

New Member
Joined
Dec 12, 2017
Messages
16
Possibly...
Code:
Dim lCol As Long
lCol = Sheets("Sheet2").Cells(2, Columns.Count).End(xlToLeft).Column + 1
Cells(2, lCol).Value = UserForm1.TextBox1.Text

Else post the code that you are using that has failed.



This was actually very similar to one of the approaches I've tried. This is my best attempt currently:

Code:
Dim LastColumn as Long
Dim ws As Worksheet
Set ws = Sheet5
LastColumn = ws.Range(2, Columns.Count).End(xlToLeft).Column + 1
Cells(2, LastColumn).Value = txtVendorName.Text

Although this still results in an error message: Error 1004, Method 'Range' of object '_Worksheet' failed.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,206
Messages
5,594,832
Members
413,943
Latest member
Dhornsby21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top