Converting a range in excel to a single column with excel vba

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 

Converting a range in excel to a single column with excel vba


4-5 hours is ridiculous

4-5 hours with ScreenUpdating, Events, & Calculation disabled is even more so.

What you've discovered here is that Excel is very slow at inserting/deleting columns/rows when you have large amounts of data in a Worksheet.

and you're doing it up to 235,000 times.

Delete everything in a single operation

What we're going to do here is loop through Ranges and, as we go, add all the Ranges-to-be-deleted to one master Range using the Union() function.

Then, at the end, delete the entire master range in one go:

Dim rowsToBeDeleted As Range '/ our master delete range

For row = Z To 1 Step -1

    If Cells(row, 1) = 0 Then

        If rowsToBeDeleted is Nothing Then '/ check if any ranges have been added yet
            Set rowsToBeDeleted = Range("A" & row) '/ add the first range
            Set rowsToBeDeleted = Union(rowsToBeDeleted, Range("A" & row)) '/ add the new range to the existing ones
        End If

    End If

Next row

If Not rowsToBeDeleted Is Nothing Then '/ check that we found anything to delete
End If

I suspect that this change alone will take your runtime from hours to minutes.

(Just as an aside, worth noting that a Range object can only have up to 1,048,576 range areas. So if you ever get up to more than 1,024^2, you'll have to check against it.)

Category: performance Time: 2016-07-28 Views: 0

Related post

  • Excel Single column into rows, VBA script insight 2012-06-04

    Okay, so much similiar to the below link but mine is a bit different. Paginate Rows into Columns in Excel I have a lot of data in column A, I want to take every 14 to 15 rows and make them a new row with multiple columns. I'm trying to get it into a

  • Getting an interval of medians in Excel, from a single column of data? 2012-02-12

    I have a quite a large number of rows of of data in the 'A' column in Excel. What I'd like to do is get the median in intervals of 8 answers (=MEDIAN(A1:A8), then the median of the next 8 answers =MEDIAN(A9:A16 and so on). How can I do this without m

  • How to extract unique data from cells in a single column with thousands of similar values? 2013-01-24

    I have a single column in an Excel sheet with around 25,000 cells (numbers). Most of them are identical that in the end there are only - I guess - maybe 200 different numbers. Is there a way to extract them? In the end, I want a list with every entry

  • Look up the next highest value in another column with Excel 2013-05-03

    I have two columns A and B. A B 20 100 40 200 60 300 80 400 140 500 190 600 240 700 User inputs a value in a cell. I need to check the value input by the user against values in Column A and output the value in Column B corresponding to the nearest hi

  • Excel auto fill a column with increment 2014-11-17

    I would like a column that increments by 1 each row, like this: 1 2 3 4 5 etc. I would like to do this for 20,000 rows. What's the easiest way to auto-fill this column on Excel for OS X? I can do it easily until the end of the sheet, but then I have

  • How to extract single column with Spring FixedLengthTokenizer 2016-01-27

    In all examples ranges are looking similar to something below and they are supposed to extract multiple colums. <bean id="fixedFileTokenizer" class="org.springframework.batch.item.file.transform.FixedLengthTokenizer"> <propert

  • Export to Excel does not export columns with multiple selections in SharePoint 2013 2015-03-26

    I am trying to export a list to Excel (Office 2013) that contains columns containing multiple selections, except the columns with multiple selections are completely missing from the exported Excel. To be specific, the type of columns that do not expo

  • Plot disproportionate values in single graph with Excel 2011-07-24

    I have multiple series of data that are on different scales and I would like to graph all of them in the same line chart based on percentage, but I want to label the points with the actual values. One series has values in 100's, another in 10's and a

  • In trello, are you able to convert a list of cards into a single card with a checklist? 2013-09-15

    I see you can convert checklist items to a card. Can you convert the other way (take a list of cards and convert to checklist)? --------------Solutions------------- There is no feature to do this in Trello. However, since you can create multiple chec

  • Grouping and sorting rows in Excel based on single column 2013-10-28

    I have a list of families I need to sort by last name in column A. You can see that each child in the family has its own row. The problem is that when you apply sort by column A descending, it does not keep families together. Instead, it sorts rows i

  • Save Excel attachment as .txt - opened with a vba macro from Outlook 2010 2016-01-15

    I have VBA code in Outlook which downloads an Excel attachment of specific emails. Once saved I open the Excel file and change a couple of things, and then I would like to save it as .txt instead of excel. This is my code: ' Save the attachment as a

  • Excel custom format url column with a tags 2013-07-29

    I have a column of url's with contents like this I need a formula in excel where I can add an a tag before and after each url, so the result is like this: <a href="

  • How do I align two tables based on a unique column with Excel? 2015-07-07

    I have two tables in excel, the first is a complete list of products with some basic info, the other has only selected products in and more information about them, but is lacking some of the information in the first table. I want to merge or align th

  • How to read and write Excel via COM object together with Excel instance manual operating 2016-01-22

    I am trying to read and write Excel via COM. And on the same time , I also want to modify the cell value manually . It seems there is an access conflict. How can I detect this conflict and avoid it/ The following is my code example. In the example, a

  • How to sum the other cells in a column with Excel? 2011-07-29

    In cell A1 I can't write =SUM(A:A) (Circular reference). If I write =SUM(A2:A15) when I insert A16 the result won't be affected. How to do this? --------------Solutions------------- To sum up all cells in the entire column except the one cell in the

  • Oracle SQL Query/SubQuery Single column with mutilple data 2012-12-17

    Id File-Name Descrption 1 yyyy Started 2 yyyy incompleted 3 rrrr Started 4 tttt started 5 uuuu started 6 rrrr completed 7 tttt completed 8 uuuu incompleted A table having three rows where I need to write a query/condition to get all the completed rec

  • Excel: return name of column with highest value in a non-continuous array 2014-01-17

    I have looked up many answers to find the highest value in a column, and return the column name. However, every answer is dependent on using a continuous array and uses the INDEX function. This is not possible for me. I need to look at the values in

  • Sorting table according to a single column with multiple values 2014-07-23

    Have a design related question. The image shows a sample table sorted according to the column 'Position'. How would one sort the column according to 'Office', with the fields having multiple values. The solutions I have, at the top of my mind - is. 1

  • Split single column data into multiple columns based on leap year in Excel 2013 2014-05-14

    I have about 100 files of MS Excel which consist of weather data. I need the data on yearly basis but the file has a single column with data of about 140 years and manually copy-paste is very time consuming. So, is there any way to split the data by

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development


Front-end development


development tools

Open Platform

Javascript development

.NET development

cloud computing


Copyright (C), All Rights Reserved.

processed in 0.868 (s). 13 q(s)