Identify row based on criteria and copy paste special values only

[S Himmelrich]

I've got the following code that does select the correct row and bolds
it, but it's not doing the copy and paste special.....can anyone help
me out on this?

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 7) = "" Then
Rows(i).Select
Selection.Font.Bold = True
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If
Next i

 

[Per Jessen]

Hi

As far as I can tell, it is doing what it is supposed to do.

Bold the selected line and substitute formulas in same line with
constant values.

But the code will run a bit faster if you skip the select statements:

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 7) = "" Then
With Rows(i)
.Font.Bold = True
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End If
Next i
Application.CutCopyMode = False

Hopes this helps.
....
Per

 

[Howard31]

Hi there,

Copy and paste the following:

Sub HighlightAndCopy()
Dim sht As Worksheet, i As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")

With sht
For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
If .Cells(i, 7) = "" Then
With .Rows(i)
.Font.Bold = True
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End If
Next i
Application.CutCopyMode = False
End With
End Sub

 

[S Himmelrich]

Hi there,

Copy and paste the following:

Sub HighlightAndCopy()
    Dim sht As Worksheet, i As Long
    Set sht = ThisWorkbook.Worksheets("Sheet1")

    With sht
        For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If .Cells(i, 7) = "" Then
                With .Rows(i)
                    .Font.Bold = True
                    .Copy
                    .PasteSpecial Paste:=xlPasteValues
                End With
            End If
        Next i
        Application.CutCopyMode = False
    End With
End Sub
--
A. Ch. Eirinberg







- Show quoted text -

Thank you!

 

[S Himmelrich]

Hi

As far as I can tell, it is doing what it is supposed to do.

Bold the selected line and substitute formulas in same line with
constant values.

But the code will run a bit faster if you skip the select statements:

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 7) = "" Then
        With Rows(i)
            .Font.Bold = True
            .Copy
            .PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        End With
    End If
Next i
Application.CutCopyMode = False

Hopes this helps.
...
Per





- Show quoted text -

Thank you!