finding empty row

JR1412

Board Regular
Joined
Jan 29, 2005
Messages
62
I need to find the first blank cell, but ignore A1 and A2 (these are reserved for other data that may not be entered yet). So the first cell available would be A3, even if the entire sheet was blank. Okay, this is the what I have
Sheets("sheet1").Range("A2003").End(xlUp).Offset(1,0).Select
How do I get it to stop trying to set A2 as active instead of A3 when the sheet is blank?

This is what I am using to input the information into several different columns in the same row found above.
Range("A3").Value = txtName.Value
Range("A4").Value = txtQty.Value
So how do I modify the range to use the active row found in the first part?
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
These aren't valid statements:

Range("A3").Value - txtName.Value
Range("A4").Value - txtQty.Value

Do you mean = instead of -
 

JR1412

Board Regular
Joined
Jan 29, 2005
Messages
62
thanks, that was a typo. I am using two pc's, one to post, one for work.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Sheets("sheet1").Range("A2003").End(xlUp).Offset(1,0).Select
if activecell.row = 2 then activecell.offset(1,0).select

Assuming that you have found a valid cell, then
activecell = txtname.value
activecell.offset(0,1).value = txtqty.value

....



Tony
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
May I offer a couple suggestions:

(1)
For the first blank row (assuming "blank" means empty, not containing a formula that returns a null string) then you should look from the top down, instead of from the bottom up.

(2)
Here is what I use, because the End(xl[Direction]) needs two occupied cells in succession at the start to make it return a correct positive:


Dim NextRow As Long
With Sheets("Sheet1")
If IsEmpty(.Range("A3")) Then
NextRow = 3
ElseIf IsEmpty(.Range("A4")) Then
NextRow = 4
Else
NextRow = .Range("A3").End(xlDown).row + 1
End If
End With


Now, if you want to place the value of what is on a textbox named txtQty into the cell in column D of that next available row of Sheet1, then:

Sheets("Sheet1").Range("D" & NextRow).Value = txtQty.Value

or

Sheets("Sheet1").Cells(NextRow, 4).Value = txtQty.Value
 

JR1412

Board Regular
Joined
Jan 29, 2005
Messages
62
it works!

after several hours of looking through the help, online and in the book, you guys solved it.

Thanks very much,
Jon
 

Watch MrExcel Video

Forum statistics

Threads
1,122,653
Messages
5,597,377
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top