# Excell selection

Say,

I've a column such as below:

6234368
7234598
8344845

I wish to
1. high lite each 4 in position 4 from the left.
2. delete each row with a 4 in position 4.

Using Excel 2013 sheet
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Finance AnalystCommented:
Assuming data starting in A3; in a neighbouring column use:

=IF(MID(A3,4,1)="4","Delete","")

Copy down as required then apply filter to this column and select "Delete" entries. You can then highlight all and delete rows, only those visible will be deleted. Remove filter and other values will stay.

Thanks
Rob H
Commented:
Assuming you want to check A Column from row-2 till last row then you can use the following code which will do what you are looking for...

``````Sub deleterows()
Dim i As Long

i = 2

Do Until i > Cells(Cells.Rows.Count, "A").End(xlUp).Row

If Trim(Mid(Cells(i, "A").Value, 4, 1)) = 4 Then
Rows(i).Delete
Else
i = i + 1
End If

Loop

End Sub
``````

Saurabh...
Author Commented:
Tx but how can I
1. highlite each 4 in position 4 from the left. or simpler to highlite each row that has a 4 in position 4
Commented:
Shaun,

Either you can highlight the row or you delete the row you can't do both like you are trying to do..In case you want to highlight use this code..it will color that entire row with red color...

``````Sub Highlightrows()
Dim i As Long

i = 2

Do Until i > Cells(Cells.Rows.Count, "A").End(xlUp).Row

If Trim(Mid(Cells(i, "A").Value, 4, 1)) = 4 Then
Rows(i).Interior.ColorIndex = 3

End If
i = i + 1
Loop

End Sub
``````

Saurabh...

Experts Exchange Solution brought to you by