select next emtpy column

[av8tordude]

How can I select then next empty column starting from column "T" on the current row selected but stop if the column "H" is filled in VBA?

[paulked]

Sub Test()
Dim lc As Long, rw As Long
rw = ActiveCell.Row
If Cells(rw, 8) <> "" Then Exit Sub
lc = Cells(rw, Columns.Count).End(xlToLeft).Column
If lc < 20 Then lc = 19
Cells(rw, lc + 1).Activate
End Sub

[av8tordude]

Sub Test()
Dim lc As Long, rw As Long
rw = ActiveCell.Row
If Cells(rw, 8) <> "" Then Exit Sub
lc = Cells(rw, Columns.Count).End(xlToLeft).Column
If lc < 20 Then lc = 19
Cells(rw, lc + 1).Activate
End Sub



Thank you Paul.

Just one thing. I made a mistake in the column criteria. Its supposed to be from "T" to "AH". What do i need to change in the code?

[paulked]

Sub test2()
Dim cl As Long
For cl = 20 To 34
If Cells(ActiveCell.Row, cl) = "" Then Cells(ActiveCell.Row, cl).Activate: Exit Sub
Next
End Sub

[av8tordude]

Thank you Paul for you help

[paulked]

You're welcome :thumb

[av8tordude]

Is there a way I can write the code without looping?


Dim cl As Long

For cl = 20 To 34 Step 3
If Cells(ActiveCell.Row, cl) = "" Then
Cells(ActiveCell.Row, cl).Resize(, 3) = Array(tbQty, tbPrice, tbFee)
Exit Sub
End If
Next

[paulked]

What are you trying to do without looping?

[av8tordude]

I'm trying to go to the first 3 empty columns (starting from column 20, ending at column 34) to insert data. If, for example, the first six column is filled then insert the data in the next column. That being said, I think looping is the only way to do that. I was just curious.


Bringing up another request, if I may ask. I have a listbox that displays the data from column 20-34. If i select an item in the listbox, how can I write a code that will insert the edited data back in the same place. (i.e. in the pic, row 2 corresponding with column 23-25)

[paulked]

It may be easier for you to write the updated listbox back to the range you read it from, clearing that range first (because you have delete in there)

[av8tordude]

Ok..I will look into it. Thx:friends: