I am very new to coding and know only the basics. The first part of this code is running fine. It converts a range of values to a single column. However, with my data set the rows of data step down, as shown in the sample data set below, so that when they are converted to a single column there are large gaps of 0 values in the column. I added a portion of code to the end to look at each cell in the column and delete any 0 values. The problem is this code takes around 4-5 hours to run. I am hoping there is a better way to write the code to speed up the processing time.
Any help is appreciated!
Sub CombineColumns() Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual Dim rng As Range Dim iCol As Long Dim lastCell As Long Dim k As Long k = 484 'set K equal to the number of data points that created the range Set rng = ActiveCell.CurrentRegion lastCell = rng.Columns(1).Rows.Count + 1 For iCol = 2 To rng.Columns.Count Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut ActiveSheet.Paste Destination:=Cells(lastCell, 1) lastCell = lastCell + rng.Columns(iCol).Rows.Count Next iCol Dim z As Long Dim m As Long z = k ^ 2 For row = z To 1 Step -1 If Cells(row, 1) = 0 Then Range("A" & row).Delete Shift:=xlUp Application.StatusBar = "Progress: " & row & " of z: " & Format((z - row) / z, "Percent") DoEvents End If Next Application.StatusBar = False Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub