Find next empty cell in a list?

razzandy

Active Member
Joined
Jun 26, 2002
Messages
390
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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>
 
Upvote 0
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
 
Upvote 0
Can anybody else help with my last posting?

Cheers

Ryan UK :)
 
Upvote 0
You mean there is no such thing as UsedRows?
Try:

If Sheets("Sheet1").UsedRange.Resize(, 1).Count < 65536 Then
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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
Back
Top