Delete xls rows depending on text format

[addy]

Hi

I have an Excel spreadsheet with approx. 6000 rows in it and I need to remove all lines where the text in Column A is underlined. Is there a simple bit of coding I could use to do this? I don't want to have to go through 6000 rows manually deleting those rows I don't require.

Thanks.

 

[SkipVought]

Hi,

You want to look at the Font Property of the cell range.

First thing that I did was macro record underlining the value in a cell and I got this code...

    Selection.Font.Underline = xlUnderlineStyleSingle

So that raises a question for me.

1. what kind of underline style are you looking for?

2. is ALL the text underlined or only PART or the text?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[addy]

The entire content of the cell is underlined in each case with a single underline.

 

[SkipVought]

So beyond that question...

With ActiveSheet.UsedRange
  for lRow = .row + .rows.count-1 to .row step -1
     if IsUnderlined(Cells(lRow, "A")) then Cells(lRow, "A").EntireRow.delete Shift=xlup
  next
End With

where IsUnderlined() is a UDF that we will write, once the original questions are answered.


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[addy]

SkipVought

Thanks for your help on this!

Anyway, the underline style is xlUnderlineStyleSingle and ALL of the text is underlined in each case.

 

[PHV]

You may try this:
Dim c As Range
With Application.FindFormat
.Clear
.Font.Underline = xlUnderlineStyleSingle
End With
With Range("A:A")
Set c = .Find(What:="", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchFormat:=True)
If Not c Is Nothing Then
Do
c.EntireRow.Delete
Set c = .Find(What:="", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchFormat:=True)
Loop While Not c Is Nothing
End If
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886