VBA Loop and convert cell to values until cell is blank

cairo95

Board Regular
Joined
Dec 11, 2007
Messages
91
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:
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
 
To convert every 3rd row starting at row 1 try
Code:
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
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
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".
 
Upvote 0
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! :)
 
Upvote 0
Have you tried the code in post#11?
 
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback.
Now it's working, this might speed things up
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top