How to enter data into the last column in a range of rows that has all blanks

[IR_Excel]

Hello,

I hope you are all well.

I am trying to work on the below code that allows me to enter data into the last column in a range of rows that has all blank cells (that is the columns starting from column 43 to the right).
In other words, it means entering data into the column after the last column that contains text or numbers.

This is currently my code:

Private Sub cmdAdd_Click()

Dim lastrow As Long, count As Long


lastrow = Sheets("Referenced Data (Do not change)").Cells(Rows.count, 41).End(xlUp).Row
lastrow = lastrow + 1
Sheets("Referenced Data (Do not change)").Cells(lastrow, 41) = cmdDrawingNo
count = 0
For i = 8 To lastrow
If cmdDrawingNo = Sheets("Referenced Data (Do not change)").Cells(i, 41) Then
count = count + 1
End If

If count > 1 Then
Sheets("sheet2").Cells(lastrow, 41) = ""
Sheets("sheet2").Cells(lastrow, 42) = ""
Sheets("sheet2").Cells(lastrow, 43) = ""
MsgBox "Duplicate entry! Name already exists!"
End If

If count = 1 Then
Sheets("sheet2").Cells(lastrow, 41) = Number.Text
Sheets("sheet2").Cells(lastrow, 42) = Name.Text
Sheets("sheet2").Cells(lastrow, 43) = MobileNo.Value
End If
Next
End Sub

Please let me know if you need clarification.

How to enter data into the last column in a range of rows that has all blanks [SOLVED]

Hi All, I hope you are all well. I am trying to work on the below code that allows me to enter data into the last column in a range of rows that has all blank cells (that is the columns starting from column 43 to the right). In other words, it means entering data into the column after the...

www.excelforum.com

 

[JLGWhiz]

This will find the last column with text or characters.

Dim col As Long
col = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column

Then to use the next column which would be blank, the col variable would be col + 1. Example: The col variable returns 40 as the last column with text or characters. You want to post a value from Sheet2!D5 into row 20 of the next available column, which would be 41.

ActiveSheet.Cells(20, col + 1) = Sheets("Sheet2").Range("D5").Value

Just use the col variable as a reference and offset from that column by either using the plus values or the offset funtion. The Offset function must be from the cell reference, i.e. (

ActiveSheet.Cells(20, col).Offset(, 1) = Sheets("Sheet2").Range("D5").Value

 

[IR_Excel]

I just realised that my question is a bit more complicated than initially thought.

What I am trying to do is to enter revision letters for documents but they have to be aligned with the date at the bottom of the sheet. I want to do this using a userform.

How woud I instruct excel to allow me to enter revision letters in any of the cells between F7-F30 using a command button in a userform, making sure it always aligns with the last date furthest to the right?

 

[IR_Excel]

I just realised that my question is a bit more complicated than initially thought.

What I am trying to do is to enter revision letters for documents but they have to be aligned with the date at the bottom of the sheet. I want to do this using a userform.

How woud I instruct excel to allow me to enter revision letters in any of the cells between F7-F30 using a command button in a userform, making sure it always aligns with the last date furthest to the right?

I noticed there was a number 2 in F29 which should not be there.

 

[IR_Excel]

Hi JLGWhiz,

Thank you for solving my query. I used the following code and it worked:

lcolumn = Sheet2.Range("A42:EW42").Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

 

[JLGWhiz]

You're welcome,
regards, JLG