Deleting/Sorting
 

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. Im 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. Im not sure how to
do this.

I would sort the records, but some of the cells I dont 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

Deleting/Sorting
 

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!!

--
-SA


"Karen" wrote:

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. Im 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. Im not sure how to
do this.

I would sort the records, but some of the cells I dont 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

Deleting/Sorting
 

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

--
-SA


"Karen" wrote:

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. Im 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. Im not sure how to
do this.

I would sort the records, but some of the cells I dont 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

Deleting/Sorting
 

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



On Jul 28, 12:23*pm, Karen wrote:
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

Deleting/Sorting
 

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
'/=======================================/


--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Karen" wrote:

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. Im 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. Im not sure how to
do this.

I would sort the records, but some of the cells I dont 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

Deleting/Sorting
 

Thank you VERY much for your help

"StumpedAgain" wrote:

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!!

--
-SA


"Karen" wrote:

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. Im 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. Im not sure how to
do this.

I would sort the records, but some of the cells I dont 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

Deleting/Sorting
 

Thank you VERY much for your help

"Gary Brown" wrote:

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
'/=======================================/


--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Karen" wrote:

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. Im 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. Im not sure how to
do this.

I would sort the records, but some of the cells I dont 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

Deleting/Sorting
 

Thank you VERY much for your help

"StumpedAgain" wrote:

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

--
-SA


"Karen" wrote:

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. Im 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. Im not sure how to
do this.

I would sort the records, but some of the cells I dont 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

Deleting/Sorting
 

Thank you VERY much for your help

"Susan" wrote:

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



On Jul 28, 12:23 pm, Karen wrote:
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. Im 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. Im not sure how to
do this.

I would sort the records, but some of the cells I dont 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