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]

How about

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

 

[cairo95]

How about

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

thank you! i ran this code and for some reason its converting every 13th cell...not sure why.

 

[Fluff]

Shouldn't be. Do you have any merged cells?

 

[cairo95]

Shouldn't be. Do you have any merged cells?

no merged cells....but it is 180000 rows ...let me run it again...takes abouot 11.5 minutes
thanks again!

Cairo95

 

[cairo95]

here is a sample of what it is doing:

=IF(B1="","","WINCMD(C/"&TEXT(B1,"00000000")&")") ' the macro skipped the first line but correctly changed to value the 4th row...which is under the words"WINCMD(CER)"
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)

'converted this line correctly

=IF(B3="","","WINCMD(C/"&TEXT(B3,"00000000")&")") 'didnt convert this line...
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)
=IF(B4="","","WINCMD(C/"&TEXT(B4,"00000000")&")")' didnt convert this line
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)
=IF(B5="","","WINCMD(C/"&TEXT(B5,"00000000")&")") ' didnt convert this line
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)
'converted this line correctly


=IF(B7="","","WINCMD(C/"&TEXT(B7,"00000000")&")")
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)
=IF(B8="","","WINCMD(C/"&TEXT(B8,"00000000")&")")
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)
=IF(B9="","","WINCMD(C/"&TEXT(B9,"00000000")&")")
WINCMD(SS/F/REFER TO ICARGO)
WINCMD(CER)

thanks again!

 

[Fluff]

If I understand correctly that's every 3rd line not every 4th line.
However are you trying to convert all formulae in the column to values?

 

[cairo95]

here is a snap shot of the excel sheet

its my understanding that line 1,4,7 and so on should be converted ...when it is converted the 2 rows beneath if should vanish. the macro should skip over the cells that vanish due to the conversion.

thanks again!
cairo95

 

[Fluff]

I cannot see that image, because it's on your computer, you would have to post it to an image share site.
From what you posted in post#6 it looks like you have a formula every 3rd row with text in between.
If that's right I don't understand how converting a formula to a value would make the 2 rows of text "vanish"

 

[cairo95]

sorry im having issues uploading my snapshot so below should help understand what im seeing after running the macro you are helping with.
so, the macro should convert the 1 line then jump to the 4the and convert, this will make rows 5 and 6 go blank and so on...next would be row 7 to convert then rows 8 and 9 should go blank..hope this helps!

1 =IF(B1="","","WINCMD(C/"&TEXT(B1,"00000000")&")")
2 WINCMD(SS/F/REFER TO ICARGO)
3 WINCMD(CER)
4
5
6
7 =IF(B3="","","WINCMD(C/"&TEXT(B3,"00000000")&")")
8 WINCMD(SS/F/REFER TO ICARGO)
9 WINCMD(CER)
10 =IF(B4="","","WINCMD(C/"&TEXT(B4,"00000000")&")")
11 WINCMD(SS/F/REFER TO ICARGO)
12 WINCMD(CER)
13 =IF(B5="","","WINCMD(C/"&TEXT(B5,"00000000")&")")
14 WINCMD(SS/F/REFER TO ICARGO)
15 WINCMD(CER)
16
17
18
19 =IF(B7="","","WINCMD(C/"&TEXT(B7,"00000000")&")")
20 WINCMD(SS/F/REFER TO ICARGO)
21 WINCMD(CER)
22 =IF(B8="","","WINCMD(C/"&TEXT(B8,"00000000")&")")
23 WINCMD(SS/F/REFER TO ICARGO)
24 WINCMD(CER)


Thanks again!
Cairo95