Match function in VBA to find the first blank in a horizontal range

[ash.mak]

Hi Guys,

I have a table that is divided in 3 blocks. Headers start from B3:BZ3 and has max of 31 rows. Problem is that the 3 block vary in horizontal dimensions daily. For example my first block would be B3:P33 on one day and B3:AE3 the other day. and same with other blocks of data. Fortunately there is atleast one blank cell in row 3 between the two blocks. I am using

RS = 2
RE = 78
iRS = 2
iRE = 0
HDRRange = Range(Cells(3, iRS), Cells(3, RE)).Address

iRE = Application.WorksheetFunction.Match("", ActiveWorkbook.Worksheets(ShtName).Range(HDRRange), 0)
SrcAdRange = Range(Cells(2, iRS), Cells(3, iRE)).Address


I am getting an error on line with worksheet function. I am not sure if matching a blank is creating the problem or I am not using the worksheetfunction properly. I am sure, I can get help here on this forum.

THNX!!!

 

[JoeMo]

If the cell contains "" as the return of a formula, match will find it, but it will not find a truly empty cell.
Try this instead:

Dim c As Range, HDRRange As Range
Set HDRRange = Range(Cells(3, iRS), Cells(3, RE))
For Each c In HDRRange
    If IsEmpty(c) Then
        iRE = c.Column
        Exit For
    End If
Next c
SrcAdRange = Range(Cells(2, iRS), Cells(3, iRE)).Address
'rest of code

 

[ash.mak]

Thanks Joe.

It seems this is the only alternative. I was using the MATCH to get faster results. Iterations do slow things down. However, your timely help is highly appreciated. Thanks again.
If anyone has an answer to the MATCH, I would like to learn that as well. This will help me understand if there is a way using MATCH any other way. I just love excel and like to learn as much as possible.

Regards.

 

[Fazza]

You might prefer FIND over looping through every cell. BTW, worksheet functions usually slow things down and are best avoided/minimised.

Sub maybe()

  Dim rngStart As Range
  Dim rngBlank As Range
  Dim strFirstBlankAd As String

  With Range("B3:CA3")    ' NOTE: this range includes an extra column after last header cell BZ

    Set rngStart = .Cells(1)
    Set rngBlank = .Find(What:="", After:=rngStart, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext)

    If Not rngBlank Is Nothing Then

      strFirstBlankAd = rngBlank.Address

      Do

        Debug.Print Range(rngStart, rngBlank.Offset(, -1)).Address
        
        Set rngStart = rngBlank.Offset(, 1)
        Set rngBlank = .FindNext(rngBlank)

      Loop While Not rngBlank Is Nothing And rngBlank.Address <> strFirstBlankAd

    End If

  End With

End Sub

To use a faster loop, load the data from the worksheet into an array. Then work in VBA with that data. Such as check for LEN(ar(element)). Working with arrays is fast.

hth

 

[ash.mak]

Thanks a million Guys. I knew I could always count on you guys for excel help. May GOD Bless you all.

 

[DaveU]

If the cell contains "" as the return of a formula, match will find it, but it will not find a truly empty cell.
Try this instead:

Dim c As Range, HDRRange As Range
Set HDRRange = Range(Cells(3, iRS), Cells(3, RE))
For Each c In HDRRange
    If IsEmpty(c) Then
        iRE = c.Column
        Exit For
    End If
Next c
SrcAdRange = Range(Cells(2, iRS), Cells(3, iRE)).Address
'rest of code

I've been programming in VBA for years, and for the first time ever I just ran into the same situation as Ash. I was pulling my hair out until I came across your reply. Thanks so much Joe.

 

[JoeMo]

I've been programming in VBA for years, and for the first time ever I just ran into the same situation as Ash. I was pulling my hair out until I came across your reply. Thanks so much Joe.

You are welcome - thanks for the reply.