Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 143
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 168
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 143
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 60
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 60
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 60
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format for font color using font color Jim Excel Worksheet Functions 2 August 29th 09 11:54 AM
Color cell font text based on cell type? How do it. Chet Excel Programming 5 November 23rd 08 10:19 PM
Delete text when font is color black CAM Excel Programming 2 June 28th 08 07:36 AM
Changing Font color based on font type or size John Excel Discussion (Misc queries) 2 February 7th 08 01:50 AM
How to change the font color using the Time format in a formula softballump Excel Discussion (Misc queries) 2 August 9th 07 03:54 AM


All times are GMT +1. The time now is 02:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"