VBA Loop and convert cell to values until cell is blank

[cairo95]

Hello,

i would like help in creating VBA code that will convert the 1st cell in the selected column...lets say column "E" and then every 4th cell after that from formulas to values until the last row.."180000" or when the next cell is blank. I found this code that works for me but you have to run it for each cell manually. if you could help construct a loop until it has gone through the column until the next cell is blank then exit.

Code:

Sub SelectEveryXRow()
'Created by Sumit Bansal at https://trumpexcel.com/ added code by cairo95
Dim MyRange As Range
Dim RowSelect As Range
Dim i As Integer
Set MyRange = Selection
Set RowSelect = MyRange.Rows(4)


For i = 4 To MyRange.Rows.Count Step 4
Set RowSelect = Union(RowSelect, MyRange.Rows(i))

Next i

Application.Goto RowSelect

' this is what i need for it to execute 

Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False



End Sub

Thank you so much!

Cairo95

 

[Fluff]

To convert every 3rd row starting at row 1 try

Sub cairo95()
   Dim i As Long
   
   For i = 1 To Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row Step 3
      Cells(i, ActiveCell.Column).Value = Cells(i, ActiveCell.Column).Value
   Next i
End Sub

 

[cairo95]

so based on the above, i would like the macro to change to value row 1,4,7,10,13,16,19,22...etc...so the macro successfully converted line 4 and 16.

hope this helps!
Cairo95

 

[Fluff]

The code in post#11 will convert rows 1, 4, 7, 10 etc in whatever column the activecell is in.
So if the active cell is A1 the A1, A4, A7 etc will be converted to values, but as row 8 & 9 look to be text I don't understand how they will "vanish".

 

[cairo95]

The code in post#11 will convert rows 1, 4, 7, 10 etc in whatever column the activecell is in.
So if the active cell is A1 the A1, A4, A7 etc will be converted to values, but as row 8 & 9 look to be text I don't understand how they will "vanish".

those cells for example A2 and A3 already have if statements written in them that are dependant on the value of cell A1 so, if A1 is blank, then so are A2 and A3 and so on....so the next cell to be changed to a value would be A4...then if A4 is blank then so are A5 and A6...this continues for about 180000 rows .

thanks again for your time!

 

[Fluff]

Have you tried the code in post#11?

 

[cairo95]

Have you tried the code in post#11?

i am running it now...but so far so good!

i will update you in about 13 minutes

 

[cairo95]

sorry for the late response! it too 1 hr and 7 minites for updates on 180000 rows but the good news is it did it perfectly!!

thank you so much for your time and help with this code!!

Best Regards
cairo95

 

[Fluff]

You're welcome & thanks for the feedback.
Now it's working, this might speed things up

Sub cairo95()
   Dim i As Long
   With Application
      .ScreenUpdating = False
      .EnableEvents = False
      .Calculation = xlCalculationManual
   End With
   For i = 1 To Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row Step 3
      Cells(i, ActiveCell.Column).Value = Cells(i, ActiveCell.Column).Value
   Next i
   With Application
      .EnableEvents = True
      .Calculation = xlCalculationAutomatic
   End With
End Sub