VBA Find first empty cell in row

[PBG]

I am trying to find the first empty cell in a row using FIND or MATCH, preferably MATCH. Below are the various ways I have attempted to write the code. in most of the examples I have tried either "" or " " to search. Also with using Find I have tried the search direction of xlprevious and xlnext. The different variations result in finding the first cell in the range, which contains text. Or it does not find an empty cell given the result is 0.

CID = Sheet1.Range("1:1").Find(" ", LookAt:=xlWhole)
CID = Sheet1.Range("1:1").Find("", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
CID = Sheet1.Range("1:1").Find("", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Row '
CID = WorksheetFunction.Match("ORCID", Sheet1.Range("1:1"), 0)
CID = WorksheetFunction.Match(True, IsEmpty(Sheet1.Range("1:1")), 0)

The reason for specifically using FIND or Match is Sheet1, used for this example, is a report which is downloaded weekly from a server. The server report has a range of cells without any empty cells in the range. The headers for each column are in row 1 and the code normally searches for the headers. The server report occasionally changes without communication of the change and the column header can be changed or the entire column is removed from the report. The users of the report do not have knowledge of VBA but have knowledge of Excel. I am retiring soon and attempting to develop instructions on how to modify the code should the column header in the server report change. In the case of the column header changing the new column header can replace the old column header. But if the column is removed from the server report, which occasionally happens, I hope and thought was to direct the user(s) to remove the text from in between the " " to search for the first empty cell in the row. Which in my tested does is not possible or I have written the code wrong. My thought was if the code finds the first empty column then it would copy empty cells, given the code places the contents of the first used row to the last used row in a column, into an array which is later paste into the report being developed. This empty column can be hidden by the user.

The following line of code represents the current method I am using for searching the server report for the headers - CID = WorksheetFunction.Match("ORCID", Sheet1.Range("1:1"), 0). ORCID is declared as a string and is assigned to one of the headers. It appears at the beginning of the sub in this manner - ORCID = "Case_ID". The instructions indicate to replace the Case_ID with the new header appearing in the server report which contains the information for the report being produced.

I have reviewed various responses to similar posts in a number of forums. My question has not been posted in any other forum.

Thank you for your time and assistance. Any assistance or suggestions are appreciated.

 

[Scott Huish]

You don't need to actually select the cell like I did. I just included it to illustrate the method.

Sub FindFirstEmptyColumn()
Dim c As Range
Set c = Range("A1").End(xlToRight).Offset(, 1)
c.Select
End Sub

 

[PBG]

Scott,

Thank you for your suggestion. I am wanting to use either FIND or MATCH given it would be difficult for the users to make that type of modification to the code given they have no knowledge of VBA.

 

[MARK858]

You can try either for the first blank or empty cell

Rows("1:1").Find("", , xlValues, xlWhole, xlByColumns, xlNext).Select

or

Rows("1:1").SpecialCells(4).Cells(1).Select

but IMHO I don't believe either are easier to maintain for someone "given they have no knowledge of VBA" than post 2 by @Scott Huish

 

[MARK858]

Or it does not find an empty cell given the result is 0

If there is a 0 in the cell it is neither blank or empty

 

[PBG]

Mark858,

Thank you for your response. Row 1 contains only text headers, no numbers or zeros. At this time there are 31 columns, but again that changes. In testing Rows("1:1").Find("", , xlValues, xlWhole, xlByColumns, xlNext).Select , of course it was modified to my needs, the result was 1 (column) which contains a text header. When I place a space between " " the results are 0. Again, there are no 0s in the header row (1) only text. Currently the system report contains 31 columns. Any other thoughts on how to modify using FIND or MATCH?

 

[MARK858]

What are you actually trying to do find the first blank cell or find the first cell after the last used used cell in the row?

of course it was modified to my needs, the result was 1 (column) which contains a text header.

I don't get this result with the Find code (Specialcells will show an error in the 1st example as there are no blank cells in the used range) that I posted, with

Book1
	A	B	C	D	E	F	G	H	I
1	Order ID	Order Date	Site	SP Name	Category Name	Ship Via	Quantity	Order Total
2	109-19-F61	19/03/2014	Off Site	Laura Callahan	Grains/Cereals	Road	6	164.5818
3	108-2-N63	02/02/2014	On Site	Margaret Peacock	Beverages	Sea	20	383.3364
4	103-22-N35	22/10/2012	On Site	Robert King	Beverages	Rail	7	145.0422
5	109-31-F89	31/03/2014	Off Site	Andrew Fuller	Dairy Products	Sea	15	398.7264
6	109-30-N84	30/03/2014	On Site	Nancy Davolio	Beverages	Road	20	343.3908
Sheet1

It selects I1

With

Book1
	A	B	C	D	E	F	G	H
1	Order ID	Order Date	Site		Category Name	Ship Via	Quantity	Order Total
2	109-19-F61	19/03/2014	Off Site	Laura Callahan	Grains/Cereals	Road	6	164.5818
3	108-2-N63	02/02/2014	On Site	Margaret Peacock	Beverages	Sea	20	383.3364
4	103-22-N35	22/10/2012	On Site	Robert King	Beverages	Rail	7	145.0422
5	109-31-F89	31/03/2014	Off Site	Andrew Fuller	Dairy Products	Sea	15	398.7264
6	109-30-N84	30/03/2014	On Site	Nancy Davolio	Beverages	Road	20	343.3908
7	104-13-N74	13/03/2013	On Site	Steven Buchanan	Produce	Road	12	349.6266
8	107-24-N51	24/11/2013	On Site	Janet Leverling	Seafood	Air	30	1034.5386
Sheet1

It selects D1

Neither code returns a number in my original code. I would look at your modification or your cell contents

Edit:

When I place a space between " " the results are 0.

It won't find anything because you are looking for a space character and not an empty string, I don't know what modification you made because .Column can't return a zero

 

[PBG]

Thank you Mark.

I will review my code for any errors next week when I return to work.

Appreciate your time.

 

[MARK858]

What are you actually trying to do find the first blank cell or find the first cell after the last used used cell in the row?

If your answer to the question above is the 2nd part then you need to go about it with a different method to any of the codes posted

 

[PBG]

Thank you for your time on this Mark.

My answer would be the 2nd part and I believe I have figured out a different method.

P