MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro for Excel


Posted by nik on July 13, 2001 12:52 PM

Hi,

I wanted to create a macro to do the following.

1. staring from row 1 to n (in current sheet)
2. (In every row) if cell1 AND cell2 are empty, delete the line.
3. (In every row) if cell1 AND cell2 are NOT empty, assign the following properties to the line :
(a) Bold
(b) Italic
(c) Shade with grey

How do I do this...

Any help is greatly appretiated...

Thanx

Nick


Posted by Ben O. on July 13, 2001 2:10 PM

If you're going to be deleting rows and using a for-next loop, it's best to go from last to first:

n = 2000
For myRow = n To 1 Step -1

You should set n to the number of the last row you want to check.

If Cells(myRow, 1) = "" And Cells(myRow, 2) = "" Then
Rows(myRow).EntireRow.Delete

Else
With Rows(myRow)
.Font.Bold = True
.Font.Italic = True
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With
End If
Next myRow


Here's the macro in its entirity:

Sub CondRowFormat()
Application.ScreenUpdating = False
n = 2000
For myRow = n To 1 Step -1
If Cells(myRow, 1) = "" And Cells(myRow, 2) = "" Then
Rows(myRow).EntireRow.Delete
Else
With Rows(myRow)
.Font.Bold = True
.Font.Italic = True
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With
End If
Next myRow
End Sub

-Ben

Posted by Dax on July 13, 2001 2:25 PM


Nick,

This isn't the quickest way to achieve what you want by any means, however, it works....

Sub YourSub()
Dim rnge As Range, RowN As Long, cl As Range
Dim r As Long
'Change this to whatever row number you
'want the macro to stop at
RowN = 100
Application.ScreenUpdating = False
Set rnge = Range(Cells(1, 1), Cells(RowN, 1))

For r = 1 To rnge.Rows.Count
If rnge.Cells(r, 1) = "" And rnge.Cells(r, 2) = "" Then
rnge.Cells(r, 1).EntireRow.Delete
ElseIf rnge.Cells(r, 1) <> "" And rnge.Cells(r, 1) <> "" Then
rnge.Cells(r, 1).Font.Bold = True
rnge.Cells(r, 1).Font.Italic = True
rnge.Cells(r, 1).Interior.ColorIndex = 15
rnge.Cells(r, 2).Font.Bold = True
rnge.Cells(r, 2).Font.Italic = True
rnge.Cells(r, 2).Interior.ColorIndex = 15
End If
Next r
Application.ScreenUpdating = True
End Sub

Hope it helps,
Dax.

Posted by Nick on July 17, 2001 12:39 PM

Hi,

Thanx for the macros....

Both the examples work perfectly....

Regards,

Nick