Excel VBA - Find function and merged cells

[bourgui]

Hi All,

I have this routine which searches for the next used cell, using the
'Find' method.

My problem is when searching by column, merged cells are skipped,
depending on the the merged range:
If I search by row and the merge area spans over sevral rows, the
merged cell is skipped. If the merged area is contained within a
single row, the merged cell is NOT skipped.
Similarly for a column, if the merged area is over several column,
'Find' skips over the cell, otherwise, the merged area is not skipped.

For example, type 'Test' in cell B2, then merge B2-D2 and call the
'Find' function on the second column, like:

Set fCell = Cells.Find(What:="*", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
(assuming you have selected B1)

The funny thing is, it works fine when I search manually, using Ctrl
+F. But even if I record the macro then run it on the same document,
it skips merged cells when the function is called from VBA!!!

I have tried setting the FindFormat to look for merged cells
(application.FindFormat.MergeCells = true, and switch the SearchFormat
parameter to true), but that didn't help.

I'm at a loss here, and this is getting quite urgent. Any help very
much appreciated!

 

[bourgui]

I should specify a few things:

I need to find the previous/next non-empty cell in a column, whether
that cell has been hidden (as part of a column or row that has been
hidden) or not.

If I search through the entire sheet, the Find function will find the
merged cell, as long as the 'SearchOrder' parameter is set to xlByRows
(won't work with xlByColumns). But if I search a particular range
(like the column in question, oe even the set of columns that contain
the merged cell, it doesn't work

 

[Clif McIrvin]

I should specify a few things:

I need to find the previous/next non-empty cell in a column, whether
that cell has been hidden (as part of a column or row that has been
hidden) or not.

If I search through the entire sheet, the Find function will find the
merged cell, as long as the 'SearchOrder' parameter is set to xlByRows
(won't work with xlByColumns). But if I search a particular range
(like the column in question, oe even the set of columns that contain
the merged cell, it doesn't work

I have not worked with that situation; but I have a case where I used a
for ... each construct and looked at the cell value when find didn't
accomplish what I wanted.

Something along the lines of:

for each c in (currentSelection) [you supply correct syntax here)
if c.value [your condition]
next c

hth

 

[bourgui]

Thanks Cliff,

I was trying to avoid iterating through the cells one by one (since
there could potentially be thousands of empty cells in-between), but I
guess I don't have much of a choice )

 

[Clif McIrvin]

Thanks Cliff,

I was trying to avoid iterating through the cells one by one (since
there could potentially be thousands of empty cells in-between), but I
guess I don't have much of a choice )

I hear you; however, that iteration seems to be pretty quick ... of
course, isn't that what find is doing anyway? So there may be
negligible performance hit from doing the iteration yourself.

 

[bourgui]

I hear you; however, that iteration seems to be pretty quick ... of
course, isn't that what find is doing anyway?  So there may be
negligible performance hit from doing the iteration yourself.

I guess. I thought the built-in function would be optimized, but
really I have no clue.
I've made a few speed tests these past couple of days and indeed, it
is really quick, even on mediocre computers.

Thanks for the help anyway, the obvious choice was the right one!

 

[Clif McIrvin]

I hear you; however, that iteration seems to be pretty quick ... of
course, isn't that what find is doing anyway? So there may be
negligible performance hit from doing the iteration yourself.

I guess. I thought the built-in function would be optimized, but
really I have no clue.
I've made a few speed tests these past couple of days and indeed, it
is really quick, even on mediocre computers.

Thanks for the help anyway, the obvious choice was the right one!


You're quite welcome.

 

[anchan]

Hi All,

I have this routine which searches for the next used cell, using the
'Find' method.

My problem is when searching by column, merged cells are skipped,
depending on the the merged range:
If I search by row and the merge area spans over sevral rows, the
merged cell is skipped. If the merged area is contained within a
single row, the merged cell is NOT skipped.
Similarly for a column, if the merged area is over several column,
'Find' skips over the cell, otherwise, the merged area is not skipped.

For example, type 'Test' in cell B2, then merge B2-D2 and call the
'Find' function on the second column, like:

Set fCell = Cells.Find(What:="*", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
(assuming you have selected B1)

The funny thing is, it works fine when I search manually, using Ctrl
+F. But even if I record the macro then run it on the same document,
it skips merged cells when the function is called from VBA!!!

I have tried setting the FindFormat to look for merged cells
(application.FindFormat.MergeCells = true, and switch the SearchFormat
parameter to true), but that didn't help.

I'm at a loss here, and this is getting quite urgent. Any help very
much appreciated!

I have got the same problem and wasted almost 3 hours on it. I use the range
object instead of cells object and its works!!

wks.Range("a1:IV65535").Find(What:="ITEM NUMBER", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
True, SearchFormat:=False)

 

[seayes]

Sub testtest1()
    Debug.Print LastRo(ActiveSheet); LastCo(ActiveSheet)
End Sub

Function LastRo(ws As Worksheet) As Long
  Set rr = ws.Cells
  Set xx = rr.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
  If Not xx Is Nothing Then LastRo = xx.Row
End Function

Function LastCo(ws As Worksheet) As Long
    Set rr = ws.Cells(1, ws.Columns.Count).EntireColumn
    Set xx = rr.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If Not xx Is Nothing Then
        LastCo = xx.Column
    Else
        Set rr = ws.Cells(1, 1).Resize(ws.Rows.Count - 1, ws.Columns.Count - 1)
        Set xx = rr.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        If Not xx Is Nothing Then LastCo = xx.Column
    End If
End Function