Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
Hello All,
I am using Excel 2007 and have a workbook with many sheets There are many Pictures on each sheet and Data is in Column B on all the sheets. The data is repeated in the same format every 6th row For eg. B1 Value is in ##.## formatted as hh:mm B2 Normal Text <<<< I want to retrain this row and delete all other Rows. B3 Name eg ABCD (same for every set of Data) B4 Text in Arial Font and Black Color (varies in length) B5 Text in Arial Font and Black Color (varies in length) B6 Text in Arial Font and Black Color (varies in length) B7 same as above B8 same as above B9 same as above B10 same as above B11 same as above B12 same as above ....... .... ...... ..... B13-B18 as above B19-24 as above I use the following macro to delete all the pics from all the worksheets which works ok. Sub deleteAllPics() Dim wks As Worksheet Dim myPict As Object For Each wks In ThisWorkbook.Worksheets For Each myPict In wks.Pictures myPict.Delete Next myPict Next wks Set wks = Nothing End Sub I want a macro (I guess 3 macros??, a single macro??) to Find and Delete Rows for the following conditons on all the Sheets a) Delete Rows where the Cell Format is ##.## formatted hh:mm - to take care of B1 in the above example b) Delete Rows where the Cell Value is = ABCD - to take care of B3 in the above example c) Delete Rows where the Cell Format is Arial Font and Font Color is Black - to take care of B4,B5,B6 in the above example After the macro is run I need to have only Rows with Normal Text (as shown in the example above) Any help would be greatly appreciated TIA Rashid Khan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
On Oct 23, 11:53*am, prkhan56 wrote:
Hello All, I am using Excel 2007 and have a workbook with many sheets There are many Pictures on each sheet and Data is in Column B on all the sheets. The data is repeated in the same format every 6th row For eg. B1 * * *Value is in ##.## formatted as hh:mm B2 * * *Normal Text <<<< I want to retrain this row and delete all other Rows. B3 * * *Name eg ABCD (same for every set of Data) B4 * * *Text in Arial Font and Black Color (varies in length) B5 * * *Text in Arial Font and Black Color (varies in length) B6 * * *Text in Arial Font and Black Color (varies in length) B7 * * *same as above B8 * * *same as above B9 * * *same as above B10 * * same as above B11 * * same as above B12 * * same as above ...... *.... ...... * ..... B13-B18 as above B19-24 *as above I use the following macro to delete all the pics from all the worksheets which works ok. Sub deleteAllPics() Dim wks As Worksheet Dim myPict As Object For Each wks In ThisWorkbook.Worksheets * * * * For Each myPict In wks.Pictures * * * * * * * * myPict.Delete * * * * Next myPict * * * * Next wks Set wks = Nothing End Sub I want a macro (I guess 3 macros??, a single macro??) *to Find and Delete Rows for the following conditons on all the Sheets a) Delete Rows where the Cell Format is ##.## formatted hh:mm - to take care of B1 in the above example b) Delete Rows where the Cell Value is = ABCD - to take care of B3 in the above example c) Delete Rows where the Cell Format is Arial Font and Font Color is Black - to take care of B4,B5,B6 in the above example After the macro is run I need to have only Rows with Normal Text (as shown in the example above) Any help would be greatly appreciated TIA Rashid Khan Hi Rashid: Here is something you can use as a model: Sub RowKiller() Dim boo As Boolean, EndOfB As Long, rKill As Range Dim i As Long Set rKill = Nothing EndOfB = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To EndOfB With Cells(i, "B") boo = (.NumberFormat = "hh:mm") Or (.Value = "ABCD") Or (.Font.Name = "Arial" And .Font.ColorIndex = 1) If boo Then If rKill Is Nothing Then Set rKill = Cells(i, "B") Else Set rKill = Union(rKill, Cells(i, "B")) End If End If End With Next If rKill Is Nothing Then Else rKill.EntireRow.Delete End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
On Oct 23, 10:53*am, prkhan56 wrote:
Hello All, I am using Excel 2007 and have a workbook with many sheets There are many Pictures on each sheet and Data is in Column B on all the sheets. The data is repeated in the same format every 6th row For eg. B1 * * *Value is in ##.## formatted as hh:mm B2 * * *Normal Text <<<< I want to retrain this row and delete all other Rows. B3 * * *Name eg ABCD (same for every set of Data) B4 * * *Text in Arial Font and Black Color (varies in length) B5 * * *Text in Arial Font and Black Color (varies in length) B6 * * *Text in Arial Font and Black Color (varies in length) B7 * * *same as above B8 * * *same as above B9 * * *same as above B10 * * same as above B11 * * same as above B12 * * same as above ...... *.... ...... * ..... B13-B18 as above B19-24 *as above I use the following macro to delete all the pics from all the worksheets which works ok. Sub deleteAllPics() Dim wks As Worksheet Dim myPict As Object For Each wks In ThisWorkbook.Worksheets * * * * For Each myPict In wks.Pictures * * * * * * * * myPict.Delete * * * * Next myPict * * * * Next wks Set wks = Nothing End Sub I want a macro (I guess 3 macros??, a single macro??) *to Find and Delete Rows for the following conditons on all the Sheets a) Delete Rows where the Cell Format is ##.## formatted hh:mm - to take care of B1 in the above example b) Delete Rows where the Cell Value is = ABCD - to take care of B3 in the above example c) Delete Rows where the Cell Format is Arial Font and Font Color is Black - to take care of B4,B5,B6 in the above example After the macro is run I need to have only Rows with Normal Text (as shown in the example above) Any help would be greatly appreciated TIA Rashid Khan One way to do this is to start at the bottom and go up with your conditions. for i= cells(rows.count,1).end(xlup).row to 2 step -1 if cells(i,1)=cond1 or cells(i,1)=cond2 or cells(i,1)=cond3 then rows(i).delete next i or for i= cells(rows.count,1).end(xlup).row to 2 step -1 if cells(i,1)<cond then rows(i).delete next i modify to suit |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
On Oct 23, 10:49*pm, James Ravenswood
wrote: On Oct 23, 11:53*am, prkhan56 wrote: Hello All, I am using Excel 2007 and have a workbook with many sheets There are many Pictures on each sheet and Data is in Column B on all the sheets. The data is repeated in the same format every 6th row For eg. B1 * * *Value is in ##.## formatted as hh:mm B2 * * *Normal Text <<<< I want to retrain this row and delete all other Rows. B3 * * *Name eg ABCD (same for every set of Data) B4 * * *Text in Arial Font and Black Color (varies in length) B5 * * *Text in Arial Font and Black Color (varies in length) B6 * * *Text in Arial Font and Black Color (varies in length) B7 * * *same as above B8 * * *same as above B9 * * *same as above B10 * * same as above B11 * * same as above B12 * * same as above ...... *.... ...... * ..... B13-B18 as above B19-24 *as above I use the following macro to delete all the pics from all the worksheets which works ok. Sub deleteAllPics() Dim wks As Worksheet Dim myPict As Object For Each wks In ThisWorkbook.Worksheets * * * * For Each myPict In wks.Pictures * * * * * * * * myPict.Delete * * * * Next myPict * * * * Next wks Set wks = Nothing End Sub I want a macro (I guess 3 macros??, a single macro??) *to Find and Delete Rows for the following conditons on all the Sheets a) Delete Rows where the Cell Format is ##.## formatted hh:mm - to take care of B1 in the above example b) Delete Rows where the Cell Value is = ABCD - to take care of B3 in the above example c) Delete Rows where the Cell Format is Arial Font and Font Color is Black - to take care of B4,B5,B6 in the above example After the macro is run I need to have only Rows with Normal Text (as shown in the example above) Any help would be greatly appreciated TIA Rashid Khan Hi Rashid: Here is something you can use as a model: Sub RowKiller() Dim boo As Boolean, EndOfB As Long, rKill As Range Dim i As Long Set rKill = Nothing EndOfB = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To EndOfB * * With Cells(i, "B") * * boo = (.NumberFormat = "hh:mm") Or (.Value = "ABCD") Or (.Font.Name = "Arial" And .Font.ColorIndex = 1) * * If boo Then * * * * If rKill Is Nothing Then * * * * * * Set rKill = Cells(i, "B") * * * * Else * * * * * * Set rKill = Union(rKill, Cells(i, "B")) * * * * End If * * End If * * End With Next If rKill Is Nothing Then Else * * rKill.EntireRow.Delete End If End Sub- Hide quoted text - - Show quoted text - Hi James, Your macro does only the last part i.e delete Rows with Arial Font and Text Black It does not delete rows with the text ABCD or Rows with hh:mm format even if I re-run the macro Also I wish to run the code on all the sheets in the workbook please Thanks for your time |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
On Oct 23, 5:13*pm, prkhan56 wrote:
On Oct 23, 10:49*pm, James Ravenswood wrote: On Oct 23, 11:53*am, prkhan56 wrote: Hello All, I am using Excel 2007 and have a workbook with many sheets There are many Pictures on each sheet and Data is in Column B on all the sheets. The data is repeated in the same format every 6th row For eg. B1 * * *Value is in ##.## formatted as hh:mm B2 * * *Normal Text <<<< I want to retrain this row and delete all other Rows. B3 * * *Name eg ABCD (same for every set of Data) B4 * * *Text in Arial Font and Black Color (varies in length) B5 * * *Text in Arial Font and Black Color (varies in length) B6 * * *Text in Arial Font and Black Color (varies in length) B7 * * *same as above B8 * * *same as above B9 * * *same as above B10 * * same as above B11 * * same as above B12 * * same as above ...... *.... ...... * ..... B13-B18 as above B19-24 *as above I use the following macro to delete all the pics from all the worksheets which works ok. Sub deleteAllPics() Dim wks As Worksheet Dim myPict As Object For Each wks In ThisWorkbook.Worksheets * * * * For Each myPict In wks.Pictures * * * * * * * * myPict.Delete * * * * Next myPict * * * * Next wks Set wks = Nothing End Sub I want a macro (I guess 3 macros??, a single macro??) *to Find and Delete Rows for the following conditons on all the Sheets a) Delete Rows where the Cell Format is ##.## formatted hh:mm - to take care of B1 in the above example b) Delete Rows where the Cell Value is = ABCD - to take care of B3 in the above example c) Delete Rows where the Cell Format is Arial Font and Font Color is Black - to take care of B4,B5,B6 in the above example After the macro is run I need to have only Rows with Normal Text (as shown in the example above) Any help would be greatly appreciated TIA Rashid Khan Hi Rashid: Here is something you can use as a model: Sub RowKiller() Dim boo As Boolean, EndOfB As Long, rKill As Range Dim i As Long Set rKill = Nothing EndOfB = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To EndOfB * * With Cells(i, "B") * * boo = (.NumberFormat = "hh:mm") Or (.Value = "ABCD") Or (.Font.Name = "Arial" And .Font.ColorIndex = 1) * * If boo Then * * * * If rKill Is Nothing Then * * * * * * Set rKill = Cells(i, "B") * * * * Else * * * * * * Set rKill = Union(rKill, Cells(i, "B")) * * * * End If * * End If * * End With Next If rKill Is Nothing Then Else * * rKill.EntireRow.Delete End If End Sub- Hide quoted text - - Show quoted text - Hi James, Your macro does only the last part i.e delete Rows with Arial Font and Text Black It does not delete rows with the text ABCD or Rows with hh:mm format even if I re-run the macro Also I wish to run the code on all the sheets in the workbook please Thanks for your time- Hide quoted text - - Show quoted text - It may be a line wrap problem in my post. Make sure the boo= is all one line, not two. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
On Oct 24, 3:47*am, James Ravenswood
wrote: On Oct 23, 5:13*pm, prkhan56 wrote: On Oct 23, 10:49*pm, James Ravenswood wrote: On Oct 23, 11:53*am, prkhan56 wrote: Hello All, I am using Excel 2007 and have a workbook with many sheets There are many Pictures on each sheet and Data is in Column B on all the sheets. The data is repeated in the same format every 6th row For eg. B1 * * *Value is in ##.## formatted as hh:mm B2 * * *Normal Text <<<< I want to retrain this row and delete all other Rows. B3 * * *Name eg ABCD (same for every set of Data) B4 * * *Text in Arial Font and Black Color (varies in length) B5 * * *Text in Arial Font and Black Color (varies in length) B6 * * *Text in Arial Font and Black Color (varies in length) B7 * * *same as above B8 * * *same as above B9 * * *same as above B10 * * same as above B11 * * same as above B12 * * same as above ...... *.... ...... * ..... B13-B18 as above B19-24 *as above I use the following macro to delete all the pics from all the worksheets which works ok. Sub deleteAllPics() Dim wks As Worksheet Dim myPict As Object For Each wks In ThisWorkbook.Worksheets * * * * For Each myPict In wks.Pictures * * * * * * * * myPict.Delete * * * * Next myPict * * * * Next wks Set wks = Nothing End Sub I want a macro (I guess 3 macros??, a single macro??) *to Find and Delete Rows for the following conditons on all the Sheets a) Delete Rows where the Cell Format is ##.## formatted hh:mm - to take care of B1 in the above example b) Delete Rows where the Cell Value is = ABCD - to take care of B3 in the above example c) Delete Rows where the Cell Format is Arial Font and Font Color is Black - to take care of B4,B5,B6 in the above example After the macro is run I need to have only Rows with Normal Text (as shown in the example above) Any help would be greatly appreciated TIA Rashid Khan Hi Rashid: Here is something you can use as a model: Sub RowKiller() Dim boo As Boolean, EndOfB As Long, rKill As Range Dim i As Long Set rKill = Nothing EndOfB = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To EndOfB * * With Cells(i, "B") * * boo = (.NumberFormat = "hh:mm") Or (.Value = "ABCD") Or (.Font.Name = "Arial" And .Font.ColorIndex = 1) * * If boo Then * * * * If rKill Is Nothing Then * * * * * * Set rKill = Cells(i, "B") * * * * Else * * * * * * Set rKill = Union(rKill, Cells(i, "B")) * * * * End If * * End If * * End With Next If rKill Is Nothing Then Else * * rKill.EntireRow.Delete End If End Sub- Hide quoted text - - Show quoted text - Hi James, Your macro does only the last part i.e delete Rows with Arial Font and Text Black It does not delete rows with the text ABCD or Rows with hh:mm format even if I re-run the macro Also I wish to run the code on all the sheets in the workbook please Thanks for your time- Hide quoted text - - Show quoted text - It may be a line wrap problem in my post. *Make sure the boo= is all one line, not two.- Hide quoted text - - Show quoted text - Hi James, I kept the boo= in one line... it still does not work. It seem while executing from down upwards it does the first part and misses the other two conditions. Any clue? Thanks for your time once again Rashid |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
James' code worked for me OK. I only had to make sure "hh:mm" is time format not custom. Rgds |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
On Oct 25, 12:54*am, " wrote:
James' code worked for me OK. I only had to make sure "hh:mm" is time format not custom. Rgds Hi The data on which I was trying the code is copy pasted from the Internet. I tried James' code on a new sheet and it worked for two conditions. Rows with hh:mm format were not deleted. I dont know how to make sure the "hh:mm" is time format. I have not seen any option in the Time Format. Only option available is in Custom. Any clues! Thanks PS: Also would you guide me how to convert the data copy pasted from the Internet to a format which will be recognised by the Macro. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
I dont know how to make sure the "hh:mm" is time format.
I have not seen any option in the Time Format. Format, Cells, Number then you will have both "Time" and "Custom" This maybe won't sound to be very practical method though. Rather than "hh:mm" value is there any other identifying values on the other columns on the same rows to be deleted ? Rgds |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
On Oct 25, 1:58*am, " wrote:
I dont know how to make sure the "hh:mm" is time format. I have not seen any option in the Time Format. Format, Cells, Number then you will have both "Time" and "Custom" This maybe won't sound to be very practical method though. Rather than "hh:mm" value is there any other identifying values on the other columns on the same rows to be deleted ? Rgds Yes you are right..when I open a new Sheet and in put 12:30 the format shown is in Custom, hh:mm. The data is in only in Col B....no other values are there. How did the code worked for you? Thanks and Regards |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
Let's try this then - with James' permission.
Sub RowKiller() Dim boo As Boolean, EndOfB As Long, rKill As Range Dim i As Long Set rKill = Nothing EndOfB = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To EndOfB With Cells(i, "B") boo = (InStr(1, .Text, ":")) Or (.Value = "ABCD") Or (.Font.Name = "Arial" And .Font.ColorIndex = 1) If boo Then If rKill Is Nothing Then Set rKill = Cells(i, "B") Else Set rKill = Union(rKill, Cells(i, "B")) End If End If End With Next If rKill Is Nothing Then Else rKill.EntireRow.Delete End If End Sub Also please visit http://www.j-walk.com/ss/excel/tips/tip62.htm for checking data types. Rgds |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows as per Text, Font Color, Font Type, Time Format
On Oct 25, 9:22*pm, " wrote:
Let's try this then - with James' permission. Sub RowKiller() Dim boo As Boolean, EndOfB As Long, rKill As Range Dim i As Long Set rKill = Nothing EndOfB = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To EndOfB * * With Cells(i, "B") * * boo = (InStr(1, .Text, ":")) Or (.Value = "ABCD") Or (.Font.Name = "Arial" And .Font.ColorIndex = 1) * * If boo Then * * * * If rKill Is Nothing Then * * * * * * Set rKill = Cells(i, "B") * * * * Else * * * * * * Set rKill = Union(rKill, Cells(i, "B")) * * * * End If * * End If * * End With Next If rKill Is Nothing Then Else * * rKill.EntireRow.Delete End If End Sub Also please visit *http://www.j-walk.com/ss/excel/tips/tip62.htmfor checking data types. Rgds Hello there, Thanks a lot ... works great! You guys are a great great help to us. Also a big thanks to James Regards Rashid |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format for font color using font color | Excel Worksheet Functions | |||
Color cell font text based on cell type? How do it. | Excel Programming | |||
Delete text when font is color black | Excel Programming | |||
Changing Font color based on font type or size | Excel Discussion (Misc queries) | |||
How to change the font color using the Time format in a formula | Excel Discussion (Misc queries) |