Find next blank row in specific column

[Jadifer]

I'm quite new to VBA and have been playing with this for days.

It's a little hard to explain: There are 3 sets of option buttons on a userform, which column the data is entered into is dependant on the permutation of these results. However, I can't work out how to look for the next empty row within a specific column - I will have to do this for 15 different columns as this is the number of potential outcomes of the 3 option buttons.

I've tried playing around with the range in the following code, but it keeps overriding my headings - data entry doesn't start until row 5.

Any help would be greatly appreciated!

Code I used for column A

If oven1.Value = True And row1.Value = True And operator.Value = True Then
a = WorksheetFunction.CountA(Range("A:A"))
Cells(a, 1) = datetext.Value
End If

Userform

Sheet data is written to

 

[Gary McMaster]

Hi Jadifer,

Welcome aboard!

Here are several commonly used statements to find the last used row or column (see comments in code).

Public Sub LastUsed()

Dim lLastRow As Long
Dim lLastCol As Long
Dim oSheet As Worksheet

Set oSheet = ActiveSheet

'Last used row in column A
lLastRow = oSheet.Range("A" & Rows.Count).End(xlUp).Row

MsgBox lLastRow

'Last used row in column D
lLastRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row

MsgBox lLastRow

'Last used column in row 16
lLastCol = ActiveSheet.Cells(16, ActiveSheet.Columns.Count).End(xlToLeft).Column

MsgBox lLastCol

'Last used column in row 2
lLastCol = oSheet.Cells(2, oSheet.Columns.Count).End(xlToLeft).Column

MsgBox lLastCol

lLastRow = oSheet.Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row ' Last row used in longest column

MsgBox lLastRow

lLastCol = Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column ' Last column used in longest row

MsgBox lLastCol

End Sub

 

[Jadifer]

Thank you very much!