Hiding rows based on character recognition

[Melvin Purvis]

I have a spread sheet which can vary from 8k to 12k lines long.

Row 1 contains header information.

Excel 2003.

Column F contains random data, for lack of a better term I'll call them serial
numbers.

I need to run through all of the rows, and look at the first character in the
serial number in Column F. If the first character is "8", I need to hide the
entire row.

Can anybody provide help with this?

Many thanks in advance!

 

[Joel]

Add an auxillary column with this formula copied down enter column

=if(left(F1,1)="8",True,False)

This will get the left most digit of the number and display either true or
False

Then use autofilter (menu Data - Filters - AutoFilter). Click on new
auxillary column at set autofilter. Then select False on pulldown tab on the
first cell of the auxillary column.

 

[Don Guillett]

Try this

Sub hiderowsif()
Rows.Hidden = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, 1)) And Left(Cells(i, 1), 1) = 8 _
Then Rows(i).Hidden = True
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message
news:[email protected]...

 

[Melvin Purvis]

The good news is, this macro didn't work

The better news is, I'm understanding enough of this stuff now that it got me
pointed in the right direction

The best news is, I was able to change it to make it work perfectly now

Thaks again!

 

[Don Guillett]

Always nice to post your final result for the archives.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message

 

[Don Guillett]

Looking back at your original post the only thing you should need to change
is
cells(i,1) to cells(i,"f") or cells(i,6)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message

 

[Melvin Purvis]

That's easy for you to say, because you know what you're doing, I don't!

When I utilized your original code, all I knew was it didn't bomb out. It ran,
but I couldn't see what it was doing. After awhile, I could see it really
wasn't doing anything.

So, I started doing some research. I finally figured out the cells(i,6) thing.
I didn't know I could use "f", that's sort of confusing.

There was also something wrong with the formatting of the f cells. I think that
has to do with coming from a mainframe database. Either way, I removed the
"isnumeric" portion and then it worked fine.

For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Left(Cells(i, 6), 1) = 8 Then Rows(i).Hidden = True
Next i


Thank you again

 

[Don Guillett]

Glad you got it figured out.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message