Hi
I have set up a system that I use for work whereas I will quote up a job with all of the materials output as a list. Each workbook is a new unit that has it's own list of materials. They are all stored in a different job folder for each job. I then have another workbook with VBA code where I click a button find the job folder and then the VBA will work it's magic and open each file within that folder copy all of the materials data from each workbook into one final workbook. It has several sheets within it that will show different data sets depending on the information I need for each job. One of the data sets is a list of materials for each of the units. I have it so it copies all of the materials data from each workbook that it opens, Pastes the values and then I have it delete all of the irrelevant data with no value. The problem is when I have a large job of 500 files or so it can take 7 hours to run through every single workbook and delete all of the data with no value. Now I know my issue is running it so it iterates through it one line at a time but I am unsure how to make it so it will sort it all so all of the no value data is at the top and it deletes that straight away then it re-sorts itself back into the order that it should be. I have guessed that it will take a 7 hour job and reduce it to less than an hour if I can figure out how to optimise it.
With my first screenshot that is my initial job unit workbook as you can see it runs from line 1 all the way through to line 289 - this is where my materials list ends. I have to copy the entire list because every unit will have a different list of materials which is listed from rows 16 through to 289 and only shows when that material is needed for the job. When this is copied over to the new workbook I can have several hundred files copied over and that adds up to hundreds of thousands of rows with many blank rows which are all deleted. My initial thoughts are to try and number each row in alphanumeric order as they are always the same amount of rows and for each new work book that is copied have a formula which will add those numbers up from the previous workbook to number alphanumerically all the way through 1- however many rows it ends up being. I would ideally get it to sort through the data and organise it so all blank data rows are at the top and have it delete all of those rows and then have it do another sort from the alphanumeric data column to go from smallest to largest to put it back in order. I am just unsure how to do this. If anyone can help on this that would be great and it would save me so much time when I'm under the pump
The code that I use to sort the data and delete the non value rows is as follows
I have set up a system that I use for work whereas I will quote up a job with all of the materials output as a list. Each workbook is a new unit that has it's own list of materials. They are all stored in a different job folder for each job. I then have another workbook with VBA code where I click a button find the job folder and then the VBA will work it's magic and open each file within that folder copy all of the materials data from each workbook into one final workbook. It has several sheets within it that will show different data sets depending on the information I need for each job. One of the data sets is a list of materials for each of the units. I have it so it copies all of the materials data from each workbook that it opens, Pastes the values and then I have it delete all of the irrelevant data with no value. The problem is when I have a large job of 500 files or so it can take 7 hours to run through every single workbook and delete all of the data with no value. Now I know my issue is running it so it iterates through it one line at a time but I am unsure how to make it so it will sort it all so all of the no value data is at the top and it deletes that straight away then it re-sorts itself back into the order that it should be. I have guessed that it will take a 7 hour job and reduce it to less than an hour if I can figure out how to optimise it.
With my first screenshot that is my initial job unit workbook as you can see it runs from line 1 all the way through to line 289 - this is where my materials list ends. I have to copy the entire list because every unit will have a different list of materials which is listed from rows 16 through to 289 and only shows when that material is needed for the job. When this is copied over to the new workbook I can have several hundred files copied over and that adds up to hundreds of thousands of rows with many blank rows which are all deleted. My initial thoughts are to try and number each row in alphanumeric order as they are always the same amount of rows and for each new work book that is copied have a formula which will add those numbers up from the previous workbook to number alphanumerically all the way through 1- however many rows it ends up being. I would ideally get it to sort through the data and organise it so all blank data rows are at the top and have it delete all of those rows and then have it do another sort from the alphanumeric data column to go from smallest to largest to put it back in order. I am just unsure how to do this. If anyone can help on this that would be great and it would save me so much time when I'm under the pump
The code that I use to sort the data and delete the non value rows is as follows
VBA Code:
Sub delrowsifzero1()
Application.ScreenUpdating = False
Dim LastRow As Long
Worksheets("Itemised Detail").Activate
On Error Resume Next
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim x As Long
ActiveWorkbook.Worksheets("Itemised Detail").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Itemised Detail").Sort.SortFields.Add Key:=Range("A:a" & LastRow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Itemised Detail").Sort
.SetRange Range("A:a" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For x = LastRow To 2 Step -1
If Cells(x, 1) = "" Or Cells(x, 1) = 0 Then
Rows(x).EntireRow.Delete
End If
Next x
Application.ScreenUpdating = True
Application.CutCopyMode = False
'Hide worksheets
Worksheets("Overall Costs").Visible = xlSheetHidden
Worksheets("Single Unit Pricing").Visible = xlSheetHidden
Worksheets("Total Hours For All Units").Visible = xlSheetHidden
Worksheets("Single Unit Hours").Visible = xlSheetHidden
End Sub
