Cycling through large areas (Columns and Rows) where there is a lot of blanks inthe middle

[dispelthemyth]

I am currently looking for a better method for iterating through lots of large areas with lots of redundant columns

e.g.
Used range = A1:ZA1000, 99% of the data is in column A and B with zome in ZA

Used Range = A10:DA15000, there is data in A - Z, BA, BD and DA, most columns go up to row 100 but BA and DA go to row 15000

Used Range = D10:HA1000, most data is in columns D, E, Z, AA and HA


As you can see the majority of the cells being searched are blank

The code i am using at the minute checks the remaining colums in each row to see if they are blank and if they are it iterates to the next row

            Set rngAny = wkbToImport.Sheets(wksSheet.name).UsedRange
            
            i = rngAny.Rows.Count
            j = rngAny.Columns.Count
            iCol = 1
            With wkbToImport.Sheets(wksSheet.name)
                  
                For iRow = 1 To i


                    For iCol = 1 To j
                           
                        On Error Resume Next
                        'Check if the rest of the section can be skipped, if it can iterate to the next row
                        If .Range(.Cells(iRow, iCol), .Cells(iRow, j)).SpecialCells(xlCellTypeBlanks).Count = .Range(.Cells(iRow, iCol), .Cells(iRow, j)).Count Then
                            Exit For
                        Else
                      'Do something

Does anyone know of any code that goes through ranges quicker?

 

[L. Howard]

Maybe try something like this, looking for "Kentucky" and highlighting with yellow.

Howard

[COLOR=#00007F]Sub[/COLOR] HighlightFindValues()

[COLOR=#007F00]'PURPOSE: Highlight all cells containing a specified values[/COLOR]
[COLOR=#007F00]'SOURCE: www.TheSpreadsheetGuru.com[/COLOR]

[COLOR=#00007F]Dim[/COLOR] fnd [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]String[/COLOR], FirstFound [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]String[/COLOR]
[COLOR=#00007F]Dim[/COLOR] FoundCell [COLOR=#00007F]As[/COLOR] Range, rng [COLOR=#00007F]As[/COLOR] Range
[COLOR=#00007F]Dim[/COLOR] myRange [COLOR=#00007F]As[/COLOR] Range, LastCell [COLOR=#00007F]As[/COLOR] Range

[COLOR=#007F00]'What value do you want to find (must be in string form)?[/COLOR]
  fnd = "Kentucky"

[COLOR=#00007F]Set[/COLOR] myRange = ActiveSheet.UsedRange
[COLOR=#00007F]Set[/COLOR] LastCell = myRange.Cells(myRange.Cells.Count)
[COLOR=#00007F]Set[/COLOR] FoundCell = myRange.Find(what:=fnd, after:=LastCell)

[COLOR=#007F00]'Test to see if anything was found[/COLOR]
  [COLOR=#00007F]If[/COLOR] [COLOR=#00007F]Not[/COLOR] FoundCell [COLOR=#00007F]Is[/COLOR] [COLOR=#00007F]Nothing[/COLOR] [COLOR=#00007F]Then[/COLOR]
    FirstFound = FoundCell.Address
  [COLOR=#00007F]Else[/COLOR]
    [COLOR=#00007F]GoTo[/COLOR] NothingFound
  [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]If[/COLOR]

[COLOR=#00007F]Set[/COLOR] rng = FoundCell

[COLOR=#007F00]'Loop until cycled through all unique finds[/COLOR]
  [COLOR=#00007F]Do[/COLOR] [COLOR=#00007F]Until[/COLOR] FoundCell [COLOR=#00007F]Is[/COLOR] [COLOR=#00007F]Nothing[/COLOR]
    [COLOR=#007F00]'Find next cell with fnd value[/COLOR]
      [COLOR=#00007F]Set[/COLOR] FoundCell = myRange.FindNext(after:=FoundCell)
    
    [COLOR=#007F00]'Add found cell to rng range variable[/COLOR]
      [COLOR=#00007F]Set[/COLOR] rng = Union(rng, FoundCell)
    
    [COLOR=#007F00]'Test to see if cycled through to first found cell[/COLOR]
      [COLOR=#00007F]If[/COLOR] FoundCell.Address = FirstFound [COLOR=#00007F]Then[/COLOR] [COLOR=#00007F]Exit[/COLOR] [COLOR=#00007F]Do[/COLOR]
      
  [COLOR=#00007F]Loop[/COLOR]

[COLOR=#007F00]'Highlight Found cells yellow[/COLOR]
  rng.Interior.Color = RGB(255, 255, 0)
  
[COLOR=#00007F]Exit[/COLOR] [COLOR=#00007F]Sub[/COLOR]

[COLOR=#007F00]'Error Handler[/COLOR]
NothingFound:
  MsgBox "No values were found in this worksheet"

[COLOR=#00007F]End[/COLOR] [COLOR=#00007F]Sub[/COLOR]