Find the first available (empty) cell on a worksheet. [Solved/Closed]

BrianGreen 973 Posts Saturday January 17, 2015Registration dateContributorStatus August 6, 2018 Last seen - Jan 17, 2015 at 01:17 PM - Latest reply:  BrianGreen
- Jan 21, 2015 at 07:28 PM
As a newbee to VBA, my boss has given me an impossible task ...

Basically I need to be able to enter one cell worth of data from one sheet of a workbook into the first available cell of a different sheet of the same workbook. The empty cell may well be in the middle of the range (A4:AL54), Unfortunately, none of the data can be sorted in any way as the data in the other cell locations is required by other macros and processes.

Whilst it would be nice for someone to write the whole macro for me I wouldnt learn anything, so just to be able to find the empty cell and select it would be ideal.

I can find many ways to find the last row/column for a worksheet, or an empty row or column, but nothing about finding an empty cell within a range of cells.

Thank you in advance.
See more 

19 replies

Best answer
vcoolio 1195 Posts Thursday July 24, 2014Registration dateModeratorStatus September 22, 2018 Last seen - Jan 19, 2015 at 03:11 PM
1
Thank you
Hi Ray,

Thanks for that . Nice!

Cheers,
vcoolio.

Thank you, vcoolio 1

Something to say? Add comment

CCM has helped 1884 users this month

Actually replace the EXIT SUB with EXIT FOR as this will allow something to happen afterwards if needed
vcoolio 1195 Posts Thursday July 24, 2014Registration dateModeratorStatus September 22, 2018 Last seen - Jan 18, 2015 at 06:57 AM
0
Thank you
Hello Brian,

If you are just wanting to find the empty cell(s) and highlight it (them), then this simple macro should do the job for you:-

Sub PickTheBlanks()

ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Select
Selection.Interior.ColorIndex = 8

End Sub


You can change the colour index to suit yourself.

Alternatively, you can just use the in-built Excel find function:

Home tab--->Editing--->Find & Select. In the menu that appears, select Go To Special. In the Go To Special dialogue box that appears, select blanks then OK.

Hopefully, this is what you were after.

Cheers,
vcoolio.
BrianGreen 973 Posts Saturday January 17, 2015Registration dateContributorStatus August 6, 2018 Last seen - Jan 19, 2015 at 09:40 AM
Thanks vcoolio.

Im not sure this is what I need, but is closer than where I was! I will investigate the commands you suggest and see if I can make it work, but ...

I want to be able to insert data into a single empty cell from another page of the workbook. I cant try your code until tomorrow, but Im guessing the first line (not the "sub" line) selects ALL empty cells. I just need the first available cell (it doesnt really matter which empty cell in the range as long as there is only one cell selected). the next empty cell will be filled when the need arises to use this macro again.

Thanks again for your help so far.
RayH > BrianGreen 973 Posts Saturday January 17, 2015Registration dateContributorStatus August 6, 2018 Last seen - Jan 19, 2015 at 11:41 AM
To expand on vcoolio's answer:


For Each c In UsedRange.SpecialCells(xlCellTypeBlanks)
c.Interior.ColorIndex = 8
Exit Sub
Next

This will find the 1st empty cell and color it.
The search is done across and then down.
BrianGreen 973 Posts Saturday January 17, 2015Registration dateContributorStatus August 6, 2018 Last seen - Jan 19, 2015 at 12:06 PM
Thank you to you both.

I will try this tomorrow and let you know that it works.

Thank you again.
BrianGreen 973 Posts Saturday January 17, 2015Registration dateContributorStatus August 6, 2018 Last seen - Jan 20, 2015 at 08:32 AM
0
Thank you
Awww :(

Thanks for your efforts so far guys, but I get the following message:

Run-time error '424':
Object required

When I debug, the "For Each c In UsedRange.SpecialCells(xlCellTypeBlanks)" line is highlighted.

The macro I have so far does this on a different sheet in this workbook ...

- checks there are no errors in data that humans put in column A,
- sorts the data
- spaces it nicely so it is presented nicely to humans.

At this point I am using the following code to switch to the required sheet where the blank spaces are (followed buy your code).

Sheets("Data").Select

For Each c In UsedRange.SpecialCells(xlCellTypeBlanks).Select
c.Interior.ColorIndex = 8
Exit For
Next


vcoolio's answer does indeed perform the same action on all empty cells.

I am sure you are correct and I am close in getting what I want, but I just cant make that final jump.

I'll keep trying and researching, but if you could spell it out so a blind man could see, then I would be so happy that I would buy you a drink (tea/coffee) whenever you asked me!

Thanks for your time and help.
There should be no SELECT at the end of the 1st line:

For Each c In UsedRange.SpecialCells(xlCellTypeBlanks)
c.Interior.ColorIndex = 8
Exit For
Next

Try that and see what happens.

vcoolio's answer selects all empty cells. mine highlights the 1st empty one.
BrianGreen 973 Posts Saturday January 17, 2015Registration dateContributorStatus August 6, 2018 Last seen > RayH - Jan 21, 2015 at 02:51 AM
Yes - Sorry - That was a result of me trying lots of things and copying and pasting what I had instead of what I wanted.

I have tried the script you suggest, with the same responce from Excel:

Run-time error '424':
Object required

Sorry for the miss-information on last post.

I also appologise for the time this is dragging on - it doesnt help with us being on different time zones. Please bear with me with this.
vcoolio 1195 Posts Thursday July 24, 2014Registration dateModeratorStatus September 22, 2018 Last seen - Jan 21, 2015 at 03:45 AM
0
Thank you
Hello Brian,

The "required object" would probably be the active sheet. So, in Ray's code, make the following minor adjustment:-
Sub PickTheBlanks()

For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks)
 c.Interior.ColorIndex = 8
 Exit For
Next
End Sub


You would be better off to use Ray's code as you will no doubt be wanting to deal with one empty cell at a time, not a whole group of them as my initial code shows.

So, its over to you Ray, I'm off to work now (for the second time today!).

Cheers Gentlemen,
vcoolio.
BrianGreen 973 Posts Saturday January 17, 2015Registration dateContributorStatus August 6, 2018 Last seen - Jan 21, 2015 at 03:53 AM
0
Thank you
GUYS ....

Thank you so much!

I finally solved this using RayH's code but with a very subtle change.

My final code was:

For Each c In Sheets("Data").UsedRange.SpecialCells(xlCellTypeBlanks)
c.Interior.ColorIndex = 8
Exit For
Next


I have no idea why I had to declare the sheet in your code as it is already the active sheet, but perhaps that will come with experience.


I really do appreciate your help - I could never have solved this without you.

As for allocating kudos, I have seen on some blogs that people are only able to allocate kudos to one person, so this has to go to RayH as he was able to narrow the selection to one cell, but If I am able to give you both credit, then I will - its my first time here so not sure quite how this goes.

Thanks again.
BrianGreen 973 Posts Saturday January 17, 2015Registration dateContributorStatus August 6, 2018 Last seen - Jan 21, 2015 at 03:58 AM
Sorry Ray - there is no options to give you any credit. If anyone can tell me how I can give you it then please let me know.
BrianGreen 973 Posts Saturday January 17, 2015Registration dateContributorStatus August 6, 2018 Last seen - Jan 21, 2015 at 05:56 AM
0
Thank you
Ah - first thought was it was good, but I tried adding titles to columns in the range A1:AL3 but leaving some of the cells empty. Now the cells are shading in on the empty cells that are in the title area. I could put data in these cells and format them to be white on white, so they are no longer empty, but is there a better solution so the macro fills in empty cells in the range A4:AL54 ?
vcoolio 1195 Posts Thursday July 24, 2014Registration dateModeratorStatus September 22, 2018 Last seen - Jan 21, 2015 at 06:50 AM
Hello Brian,

Try this minor alteration:-

Change:


For Each c In Sheets("Data").UsedRange.SpecialCells(xlCellTypeBlanks) 


to:

For Each c In Sheets("Data").Range("A4:AL54").SpecialCells(xlCellTypeBlanks) 


This should take care of the titles problem.

Cheers,
vcoolio.
vcoolio 1195 Posts Thursday July 24, 2014Registration dateModeratorStatus September 22, 2018 Last seen > vcoolio 1195 Posts Thursday July 24, 2014Registration dateModeratorStatus September 22, 2018 Last seen - Jan 21, 2015 at 07:09 AM
Or, if you don't want to be locked in to a fixed range, try:-

For Each c In Sheets("Data").UsedRange.Offset(3, 0).SpecialCells(xlCellTypeBlanks)


Cheers,
vcoolio.
BrianGreen 973 Posts Saturday January 17, 2015Registration dateContributorStatus August 6, 2018 Last seen > vcoolio 1195 Posts Thursday July 24, 2014Registration dateModeratorStatus September 22, 2018 Last seen - Jan 21, 2015 at 09:01 AM
Absoluteley Marvelous!

Thank you so much!

I had forgotten about the offset command dispite using it so much in an earlier script. Im getting old and forgetful!

Oh the irony - its taking me so long to learn new stuff because Im of the age that the old stuff is forgotten ...

Thanks again.
Thanks Brian and vcoolio.
Nice collaboration.
vcoolio 1195 Posts Thursday July 24, 2014Registration dateModeratorStatus September 22, 2018 Last seen - Jan 21, 2015 at 07:01 PM
0
Thank you
Hi Brian,

I'm glad that it has all worked out for you.

@Ray:-

Thanks Ray. Nothing like collaboration. Three heads are better than one!!??!!

Cheers Gentlemen,
vcoolio.
I'm not sure I was one of the heads!

Thanks again.