Goto cells that do NOT contain a keyword on Excel 2007

MANAHMAN

New Member
Joined
Feb 16, 2021
Messages
5
Office Version
  1. 2007
Platform
  1. Windows
Hi,

I have a simple question on Excel 2007.

I have a very very large spreadsheet e.g. 10000 rows x 10000 columns. Almost all the cells store the same value e.g. "APPLE" but a few cells contain other "unknown" values. Now, i just want to locate the cell that is not equal to "APPLE". i.e. The cursor goes to one of the cells that doesn't contain "APPLE". Conditional formatting doesn't help as spread sheet is huge. Maybe all the cells contain "Apple" or just 1 cell does not contain "Apple"

We can simply use FIND to locate "APPLE" but is there any other way to locate the cell which doesn't store "APPLE" ?

Thx.

MANAHMAN
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Try this macro. Please note that "Apple" is case sensitive.
VBA Code:
Sub test()
    Dim a As Variant, i As Long, ii As Long
    Application.ScreenUpdating = False
    With Sheets("Sheet1").[a1].CurrentRegion
        a = .Value
        For i = LBound(a) To UBound(a)
            For ii = LBound(a, 2) To UBound(a, 2)
                If a(i, ii) <> "Apple" Then
                    Cells(i, ii).Select
                    Exit Sub
                End If
            Next ii
        Next i
    End With
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
Mumps,

Don't forget to add
VBA Code:
Application.ScreenUpdating =True
back in at the end of your code.

Also, should mention that using CurrentRegion will only work if there are no completely blank rows or columns in the middle of the data.
If there are, you will need to find the last cell, and build the range to search baased on that.
 

MANAHMAN

New Member
Joined
Feb 16, 2021
Messages
5
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Thx both but besides of VBA, is there any other way to make it?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
Thx both but besides of VBA, is there any other way to make it?
If it was just a single column, you could use filters and say "Not Equal to Apple". But since you have thousands of columns, that won't work.
I do not know any way of doing this without VBA.
 

MANAHMAN

New Member
Joined
Feb 16, 2021
Messages
5
Office Version
  1. 2007
Platform
  1. Windows
If it was just a single column, you could use filters and say "Not Equal to Apple". But since you have thousands of columns, that won't work.
I do not know any way of doing this without VBA.
Thx Joe4,

What about if i accept "not-nice" result ?

Original expected result : The cursor moves to "non-Apple" cell.
New expected result : Excel just tells me "YES" or "No" by all means. i.e. Yes - means all cells are "Apple"; No - means at least 1 cell doesn't contain "Apple". The result presentation can be anything as i just want to know Yes or No.

thx.

MANAHMAN
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Do all cells in the range except ONE contain "apple"
(other cell can either be empty or contain "pear")
Amend A1:H10 to the range containing your data and try this formula - it is not case sensitive.
Excel Formula:
=COUNTIF(A1:H10,"apple")=(ROWS(A1:H10)*COLUMNS(A1:H10))

If the cell that does not contain "apple" is empty then a simpler formula can be used
Excel Formula:
=COUNTBLANK(A1:H10)=0
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

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
Top