Finding First Available Empty Cell In A Defined Range

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,871
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,056
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,871
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,056
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,056
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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:

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,871
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,979
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I'd be interested to know what should happen if AJ1:AJ8 are all filled (that is, none are blank)?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,979
Office Version
  1. 2016
Platform
  1. Windows
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
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,871
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,930
Messages
5,639,047
Members
417,066
Latest member
rhenman

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