VBA - pasting visible rows as values

[jon1315]

good afternoon
I have table with various calculations. Depending on certain criteria, I would like to paste the formulas in the row to values.

I've written a macro to apply filters to determine which rows need to be pasted as values (which works and gives a correct filtered list), and then I've written the following:

Set rng = Range("au3:cj" & lrow).SpecialCells(xlCellTypeVisible)
For Each cell In rng
cell.Copy
cell.PasteSpecial xlPasteValues
Next cell

Which seems to work, but I've underestimated the amount of cells and it seems to be taking forever. I've stopped the code after 5 mins or so and it was nowhere near finishing.

Ideally, I'd like to change the "Copy every visible cell and paste as values" to "Copy every visible row and paste as values" (as there's nothing on the visible rows that need to be formula driven, so hopefully speeding up the process) but I can't seem to make it work.

If anyone could point me in the right direction I'd appreciate it!

Thank you
Jon

 

[Fluff]

How about

Sub jon()
   Dim Rng As Range
   
   For Each Rng In Range("AU3:AU" & lRow).SpecialCells(xlVisible)
      With Rng.EntireRow
         .Value = .Value
      End With
   Next Rng
End Sub

 

[jon1315]

Worked perfectly - thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jon1315]

Sorry to revisit this- more for my understanding and use in the future, if I wanted to do something else with each row - how would you go about referencing each row inside the loop?

I've had a play, and I suppose what I'm trying to ask is something like the below (where I'm trying to use the loop to put "Test" in each visible cell in CJ

For Each rng In Range("a3:cg" & LRow).SpecialCells(xlVisible)
i = rng.Row
Range("cj" & i).Value = "Test"
Next rng

But this doesn't flick the I to the next visible cell when the loop .. well.. loops!

I know it could be done by highlighing the range in CJ and using a specialcells value = "test", but I think the above shows what I'm getting at best - trying to reference each row number of a filtered list individually.

 

[Fluff]

You would do it like

   For Each Rng In Range("CJ3:CJ" & lRow).SpecialCells(xlVisible)
      Rng.Value = "Test"
   Next Rng