Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting and Deleting Help | Excel Worksheet Functions | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
Deleting data by sorting dates | Excel Discussion (Misc queries) | |||
Sorting and deleting empty cells | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions |