Deleting Rows with Bold, Underline and Italic

rchansen2001

New Member
Joined
Nov 30, 2016
Messages
28
I am trying to write VBA script that will delete the entire row for any cells in Columns R:T when the text in the corresponding cell is Bold, Underline and Italic. Example: The column heading represent a date and the row heading represents a job number. Each cell represents the number of days remaining for the job. If the number in the cell is Bold, Underline and Italic the job is stopped and I want it to be deleted so it doesn't alter my results.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Hi there,

Just a very simple example. Remove 'Me' and reference the appropriate sheet in using the code in a Standard Module.
Excel Workbook
RST
2469483278
3162413445
4298338205
572415261
68278449
7435142377
8220353489
912438373
10495107291
1117811578
1212526299
13361337118
14372338395
15435402
Sheet1
Excel 2010

Rich (BB code):
Option Explicit
  
Sub example()
Dim lRow As Long, lCol As Long


  For lRow = 15 To 2 Step -1
    For lCol = 0 To 2
      With Me.Cells(lRow, 18 + lCol).Font
        If .Bold And .Italic And CBool(.Underline = 2) Then
          Me.Rows(lRow).Delete
          Exit For
        End If
      End With
    Next
  Next
  
End Sub

Hope that helps,

Mark
 

rchansen2001

New Member
Joined
Nov 30, 2016
Messages
28
I tried the code and nothing happened. I placed it in the same personal module that I have all my other macros for the same document and tried to just run the one you gave. I tried it with 'Sheet1' and 'ActiveSheet' in place of 'me' and both times nothing happened.

Hi there,

Just a very simple example. Remove 'Me' and reference the appropriate sheet in using the code in a Standard Module.

Sheet1
RST
2469483278
3162413445
4298338205
572415261
68278449
7435142377
8220353489
912438373
10495107291
1117811578
1212526299
13361337118
14372338395
15435402

<thead>
</thead><tbody>
</tbody>
Excel 2010



Rich (BB code):
Option Explicit
  
Sub example()
Dim lRow As Long, lCol As Long


  For lRow = 15 To 2 Step -1
    For lCol = 0 To 2
      With Me.Cells(lRow, 18 + lCol).Font
        If .Bold And .Italic And CBool(.Underline = 2) Then
          Me.Rows(lRow).Delete
          Exit For
        End If
      End With
    Next
  Next
  
End Sub

Hope that helps,

Mark
 

rchansen2001

New Member
Joined
Nov 30, 2016
Messages
28
I am able to get the following code to work. But I need it to include columns 18:20 and also include underline. Currently It only works with 1 column and I must manually change to the next column. When I add on to this code to include the additional variables it breaks the code.

Code:
Sub DeleteStoppedJobs()
 Dim wks As Worksheet
 Dim iRow As Integer
 iRow = 1
 Set wks = Application.ActiveSheet
 Do Until wks.Cells(iRow, 19).Value = ""
 If wks.Cells(iRow, 19).Font.Bold = True And _
 wks.Cells(iRow, 19).Font.Italic = True Then
 wks.Rows(iRow).Delete
 Else
 iRow = iRow + 1
 End If
 Loop
 End Sub
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
I tried the code and nothing happened. I placed it in the same personal module that I have all my other macros for the same document and tried to just run the one you gave. I tried it with 'Sheet1' and 'ActiveSheet' in place of 'me' and both times nothing happened.

What exactly are you referring to as a "personal module"?

From the example data in post #2, by my understanding, you would only want rows 5 and 8 deleted; is that correct?

Mark
 

Watch MrExcel Video

Forum statistics

Threads
1,133,539
Messages
5,659,392
Members
418,500
Latest member
Guru Prasad S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top