Finding First Available Empty Cell In A Defined Range

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I'm having an issue wrapping my head around this very basic problem. I am simply trying to find the next unoccupied cell in a range (AJ1:AJ8). My code below results in an error "Object variable or With block variable not set." It could be because all cells in that range are currently empty.

Code:
with ws_thold
     drow = .Range("AJ1:AJ8").Find("*", , xlValues, , xlByRows, xlPrevious)
end with
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
It needs the word Set in front of drow and you will get a different error if all the cells are empty once that is sorted as you are looking for a cell with data in the code.
 
Last edited:
Upvote 0
Thanks Mark,

Things got pretty messy trying to fix things. I think I can manage the same thing with this, but we'll see.
Code:
drow = Application.WorksheetFunction.CountA(.Range("AJ1:AJ8")) + 1
 
Upvote 0
I didn't post any code correcting it because you need to clarify
A) if it is the first empty cell after the last cell with a value that you want or do you just want the row number?
B) what you are doing with the cell after it is found (might be answered by A)?
C) what you want to happen if there is no data in the range?

I wouldn't resort to using Counta to define last rows because of the issues with blank cells
 
Upvote 0
I am just about to knock off for the night and so assuming that you want the row number and if empty then the first cell in the range then try

VBA Code:
    Dim drow As Range, myrow As Long, ws_thold As Worksheet
  
    Set ws_thold = Sheet6                        ' change sheet reference to suit
    Set drow = ws_thold.Range("AJ1:AJ8").Find("*", , xlValues, , xlByRows, xlPrevious)
  
    If Not drow Is Nothing Then
        myrow = drow.Offset(1).Row
    Else
        myrow = 1
    End If

    MsgBox myrow

Although you will also have to think what you want to happen if AJ8 has data because the code currently will answer 9
 
Last edited:
Upvote 0
Hi Mark, I am a bit apprehensive about using counta also.

I am basically building a dynamic range. I need the next available open cell in that range to deposit a value. The next value will be placed on the next open cell in that range etc.

A) if it is the first empty cell after the last cell with a value that you want or do you just want the row number? Row number (drow)
B) what you are doing with the cell after it is found (might be answered by A)? worksheet.cells(drow,39) = variable value
C) what you want to happen if there is no data in the range? drow = 1 (start building the range)
 
Upvote 0
I'd be interested to know what should happen if AJ1:AJ8 are all filled (that is, none are blank)?
 
Upvote 0
I'd still like to know what should happen if all the cells are filled (that is, there is no blank in AJ1:AJ8), but assuming there will be a blank or that you have error trapping code to handle it, you can get the row number using this single line of code...
VBA Code:
drow = Range("A1:A8").Find("", Range("A8"), xlValues, xlWhole, , xlNext, , False).Row
I would point out, however, that if your plan is to assign a value to that blank cell, you can do so directly without getting the row number...
VBA Code:
Range("A1:A8").Find("", Range("A8"), xlValues, xlWhole, , xlNext, , False) = SomeValue
 
Upvote 0
Hi Rick,
The application would only send a maximum of 8 cells of data, so there would be no chance that a 9th value could be sent to the full range. Once the maximum of 8 values is reached, the code to add more ends.
You've given me a few options to play with, which once I can get back to it, I'll see what kind of results they net.
Always appreciate tyour insight!
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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