Find next empty cell in a list?

razzandy

Active Member
Joined
Jun 26, 2002
Messages
388
Office Version
  1. 2007
Platform
  1. Windows
I currently use the below code to find the next empty cell in a database ready for the next data input! Is there any other way of performing this action without actually selecting the cell? In other words removing the (.Select) from the code!

Sheets("Sheet1").Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select

I’m sure I’ve seen some code like Sheets(“Sheet1”).UsedRange.Offset(1, 0).Set I’m not sure though!

Can anybody suggest anything?

Any help will be much appreciated

Cheers

Ryan UK :)
This message was edited by razzandy on 2002-09-19 16:00
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Hi,

The following gives you the next available row and the first cell address in the row.

<pre>Sub test()
Dim NextRow As Long, Rng As Range

With Sheets("Sheet1")
NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Set Rng = .Cells(NextRow, 1)
End With

End Sub</pre>
 

razzandy

Active Member
Joined
Jun 26, 2002
Messages
388
Office Version
  1. 2007
Platform
  1. Windows
Cheers Jay That’s what I call cool code! :)

Here's what I've got up to now:

Private Sub CommandButton1_Click()

Dim NextRow As Long, Rng As Range

If Sheets("Sheet1").UsedRows.Count< 65536 Then
With Sheets("Sheet1")
NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Set Rng = .Cells(NextRow, 1)
Rng.Value = TextBox1.Value
Set Rng = .Cells(NextRow, 2)
Rng.Value = TextBox2.Value
End With
End If
End Sub

Notice the If statement I've added at the top of the code? I'm trying to safe guard the data so it doesn’t become overwritten if the sheet hits the limit, row 65536, but as you can see I’m not having much success! Is there any chance you or anybody can help me out a bit more?

Many Thanks

Ryan UK :)
This message was edited by razzandy on 2002-09-19 18:57
 

razzandy

Active Member
Joined
Jun 26, 2002
Messages
388
Office Version
  1. 2007
Platform
  1. Windows
Can anybody else help with my last posting?

Cheers

Ryan UK :)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

You mean there is no such thing as UsedRows?
Try:

If Sheets("Sheet1").UsedRange.Resize(, 1).Count < 65536 Then
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Ryan,

Alternatively, how about testing the rng value? See below (amended your routine slightly for testing).

<pre>
Sub RowTest()
Dim NextRow As Long, Rng As Range

With Sheets("Sheet1")
NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Set Rng = .Cells(NextRow, 1)
If Rng.Value = Empty Then
MsgBox "OK - its empty!"
Else
MsgBox "DANGER WILL ROBINSON - its not empty!"
End If
Rng.Value = 123
Set Rng = .Cells(NextRow, 2)
Rng.Value = 456
End With

End Sub

</pre>

HTH
 

razzandy

Active Member
Joined
Jun 26, 2002
Messages
388
Office Version
  1. 2007
Platform
  1. Windows
Cheers Richie

Thats just what I was after :)

Do u mind if I kiss U :wink: Haa Haaa

Just had to tweek it slightly!

Sub RowTest()
Dim NextRow As Long, Rng As Range

With Sheets("Sheet1")
NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Set Rng = .Cells(NextRow, 1)
If Rng.Value = Empty Then
MsgBox "OK - its empty!"
Rng.Value = 123
Set Rng = .Cells(NextRow, 2)
Rng.Value = 456
Else
MsgBox "DANGER WILL ROBINSON - its not empty!"
End If

End With

End Sub

Cheers Mate :)

Ryan UK
This message was edited by razzandy on 2002-09-20 05:23
 

Forum statistics

Threads
1,144,220
Messages
5,723,085
Members
422,477
Latest member
pete101

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