Help with Last Column

[Adam88]

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

 

[mumps]

This code will give you the column number of the first empty column in row 2 of "Sheet2".

Dim lCol As Long
lCol = Sheets("Sheet2").Cells(2, Columns.Count).End(xlToLeft).Column + 1

 

[Joe4]

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:

    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]

@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]

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.

 

[mumps]

@Joe4: Thanks once again.

 

[Joe4]

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]

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]

Possibly...

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.

 

[Adam88]

Possibly...

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:

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.