Deleting/Sorting

[Karen]

Using Excel 2003

I have an Excel report that has over 2,000 rows. I want to delete the rows
where the text/numbers are NOT bold and keep the ones that ARE bold. I’m not
sure if this is possible. If this cannot be done based on that criterion, the
rows I want to keep all have a number value in column E. Maybe it's possible
to isolate only the rows that have a value in column E. I’m not sure how to
do this.

I would sort the records, but some of the cells I don’t want are merged. At
least all of the rows I want to keep do not have any merged cells. Is this
possible? The range of data is A2:E2169.

Any help would be greatly appreciated – Thanks, Karen

 

[StumpedAgain]

Try something like the following:

Sub ForKaren()

Dim curselection As Range

Set curselection = Range("E2") 'or wherever your values start

Do Until curselection = ""

If IsNumeric(curselection) Then
Set curselection = curselection.Offset(1, 0)
Else:
Set curselection = curselection.Offset(1, 0)
curselection.Offset(-1, 0).EntireRow.Delete
End If

Loop

End Sub

This looks in Column E to determine if the value is numeric. If it is, it
moves on. If it isn't, it will delete that row and then look at the next. I
hope this helps!!

 

[StumpedAgain]

You could also do the following which looks for the bold format:

Sub ForKaren()

Dim curselection As Range

Set curselection = Range("E2") 'or wherever your values start

Do Until curselection = ""

If curselection.Font.Bold = True Then
Set curselection = curselection.Offset(1, 0)
Else:
Set curselection = curselection.Offset(1, 0)
curselection.Offset(-1, 0).EntireRow.Delete
End If

Loop

End Sub

 

[Susan]

this is vba code............ you can't do what you want using formulas
only. save your worksheet before running this. the code goes in a
general module and looks for bold from columns A through E. post back
if you need assistance getting the code in the proper place & running
it.
===========================
Option Explicit

Sub karen()

Dim myLastRow As Long
Dim r As Long
Dim c As Range

myLastRow = ActiveSheet.Cells(10000, 1).End(xlUp).Row

For r = myLastRow To 1 Step -1
Set c = ActiveSheet.Range("a" & r)
If c.Font.Bold Or _
c.Offset(0, 1).Font.Bold Or _
c.Offset(0, 2).Font.Bold Or _
c.Offset(0, 3).Font.Bold Or _
c.Offset(0, 4).Font.Bold Then
c.EntireRow.Delete
End If
Next r

End Sub
==========================
hope it helps!

susan

 

[Gary Brown]

This will look for bold cells in column A. When it finds them, it will
delete the row that the bold cell is in.
'/=======================================/
' Sub Purpose: Delete all rows where
' the Cell in Column A is BOLD
'/=======================================/
'
Sub DeleteBoldRows()
Dim rng As Range
Dim rCell As Range

On Error GoTo err_Sub

Set rng = Range("A:A")

For Each rCell In rng
If TypeName(Application.Intersect(rCell, _
(ActiveSheet.UsedRange))) = "Nothing" Then
Exit For
End If

If rCell.Font.Bold = True Then
rCell.Rows.Delete Shift:=xlUp
End If

Next rCell

exit_Sub:
On Error Resume Next
Set rng = Nothing
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description
GoTo exit_Sub
End Sub
'/=======================================/

 

[Karen]

Thank you VERY much for your help

Try something like the following:

Sub ForKaren()

Dim curselection As Range

Set curselection = Range("E2") 'or wherever your values start

Do Until curselection = ""

If IsNumeric(curselection) Then
Set curselection = curselection.Offset(1, 0)
Else:
Set curselection = curselection.Offset(1, 0)
curselection.Offset(-1, 0).EntireRow.Delete
End If

Loop

End Sub

This looks in Column E to determine if the value is numeric. If it is, it
moves on. If it isn't, it will delete that row and then look at the next. I
hope this helps!!

 

[Karen]

Thank you VERY much for your help

This will look for bold cells in column A. When it finds them, it will
delete the row that the bold cell is in.
'/=======================================/
' Sub Purpose: Delete all rows where
' the Cell in Column A is BOLD
'/=======================================/
'
Sub DeleteBoldRows()
Dim rng As Range
Dim rCell As Range

On Error GoTo err_Sub

Set rng = Range("A:A")

For Each rCell In rng
If TypeName(Application.Intersect(rCell, _
(ActiveSheet.UsedRange))) = "Nothing" Then
Exit For
End If

If rCell.Font.Bold = True Then
rCell.Rows.Delete Shift:=xlUp
End If

Next rCell

exit_Sub:
On Error Resume Next
Set rng = Nothing
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description
GoTo exit_Sub
End Sub
'/=======================================/

 

[Karen]

Thank you VERY much for your help

You could also do the following which looks for the bold format:

Sub ForKaren()

Dim curselection As Range

Set curselection = Range("E2") 'or wherever your values start

Do Until curselection = ""

If curselection.Font.Bold = True Then
Set curselection = curselection.Offset(1, 0)
Else:
Set curselection = curselection.Offset(1, 0)
curselection.Offset(-1, 0).EntireRow.Delete
End If

Loop

End Sub

 

[Karen]

Thank you VERY much for your help

this is vba code............ you can't do what you want using formulas
only. save your worksheet before running this. the code goes in a
general module and looks for bold from columns A through E. post back
if you need assistance getting the code in the proper place & running
it.
===========================
Option Explicit

Sub karen()

Dim myLastRow As Long
Dim r As Long
Dim c As Range

myLastRow = ActiveSheet.Cells(10000, 1).End(xlUp).Row

For r = myLastRow To 1 Step -1
Set c = ActiveSheet.Range("a" & r)
If c.Font.Bold Or _
c.Offset(0, 1).Font.Bold Or _
c.Offset(0, 2).Font.Bold Or _
c.Offset(0, 3).Font.Bold Or _
c.Offset(0, 4).Font.Bold Then
c.EntireRow.Delete
End If
Next r

End Sub
==========================
hope it helps!

susan