VBA - Finding Next Empty Row

[rafikarp]

Hi all,

Here is a VBA query which probably has a simple solution...

I have designed a user form which when you enter data into it and hit "ok" it sends the information to a worksheet named "data". For each new record entered into the form, the code I have used looks for the next empty row in the "data" sheet and adds the information. Here is the code:

ActiveWorkbook.Sheets("Data").Select
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(NextRow, 1) = FamNameTB.Text
Cells(NextRow, 2) = etc, etc, etc

My problem is I need this to look for the next empty row, but start inputing the data in column B and leave column A blank. At the moment it sends the data to the first blank row starting in column A. I would have thought simply changing the range from Range("A:A") to ("B:B") would work but it doesn't. I have tried using the offset function but this also has not worked as my coding was probably incorrect. Any suggestions? Thanks.

 

[VoG]

Try

NextRow = Range("B" & Rows.Count).End(xlUp).Row + 1

 

[Weaver]

Cells(NextRow, 1) = FamNameTB.Text

the 1 is the column

Just increment this number by 1

obviously you'd count column b

or you could use

NextRow = range("B" & rows.count).end(xlup).row + 1

 

[MrKowz]

Welcome to the boards!

Try:

NextRow = Sheets("Data").Range("B" & rows.count).End(xlUp).Row + 1

 

[Norie]

Well I've 2 suggestions to start with.

1 Don't use Select, instead qualify the worksheet reference.

2 Don't use CountA.

If you want to find the next empty row in column A try something like this.

With Sheets("Data")
 
     NextRow = .Range("A" & Rows.Count).End(xlUp).Row+1
 
     .Range("B" &  NextRow) = TextBox1.Value
End With

Note this is psuedo code as the code you posted and your explanation are hard to follow.

 

[rafikarp]

Thanks for the quick replies. All sorted now. Much appreciated.

 

[elmic]

Thanks all for your posts. please could you inform me how I select the last 3 cells of a range ex. b.. c.. d.. (..=last cells number) and then merge and format so I can write data on it? Is it possible?

 

[elmic]

Sub Sheet1()

Sheets("Raw Data").Select
With ActiveSheet

NextRow = Range("B" & Rows.Count).End(xlUp).Row + 1

End With

ActiveSheet.Cells(NextRow + 2, 1).Value = "AAAAA SSSSSSSS DDDDDDD FFFFFF "
End Sub