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

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
664
Office Version
  1. 365
Platform
  1. Windows
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

Code:
            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?
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Maybe try something like this, looking for "Kentucky" and highlighting with yellow.

Howard

Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top