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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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