Identify row based on criteria and copy paste special values only

  • Thread starter Thread starter S Himmelrich
  • Start date Start date
S

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
 
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
 
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
 
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 -
Click to expand...

Thank you!
 
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 -
Click to expand...

Thank you!
 
Back
Top