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

Question

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.

vcoolio · Answer

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.

vcoolio · Answer

Hi Ray,

Thanks for that . Nice!

Cheers,
vcoolio.

BrianGreen · Answer

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.

vcoolio · Answer

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 · Answer

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 · Answer

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 · Answer

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.

Find the first available (empty) cell on a worksheet.

7 responses

Similar discussions

Subscribe To Our Newsletter!