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

#### dispelthemyth

##### Well-known Member
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### L. Howard

##### Well-known Member
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=#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]
[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]``````

Replies
2
Views
325
Replies
10
Views
2K
Replies
8
Views
469
Replies
1
Views
276
Replies
0
Views
405

1,191,177
Messages
5,985,138
Members
439,941
Latest member
robertv13

### 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.

### Which adblocker are you using?

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

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