1

I'm sure this is probably out there but I can't seem to find it.

Updates

I have tested some different code, and reduced my time to complete the task from 50/60 seconds to 10 seconds. This is what I changed:

For k = 0 To 28

    For v = 1 To countryLastRow(k)
        If Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range("C6") Then
             Sheet3.Range(Sheet3.Cells(v + 3, countryRanges(k)), Sheet3.Cells(v + 3, countryRanges(k) + 6)).Copy Destination:=tbl.DataBodyRange(p, countryRanges(k) - 1).End(xlUp).Offset(1, 0)
        End If
    Next v
Next k

Using the range to copy and paste whole rows at once rather than 1 cell at a time has drastically improved my macro.

Still looking out their for any more performance increases people know with the code I have but this seems to be good now.

Basic rundown

In Excel I have multiple rows and multiple columns that need to be searched through then depending on criteria, copy it and paste it into another tab using VBA.

I want to find the fastest/most efficient way of coding this search/copy/paste function.

What I've Tried

So I went for a simple brute force code where I search through all cells in the Sheet2 table columns, use if formula to match the criteria then .copy destination:= to copy the row into the next empty row of table in Sheet1 then move onto the next Sheet2 table and repeat until all 29 tables have been searched.

Quite quickly I found I needed to improve the speed of this and I added the following:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

which improved the speed when I was only using one table. Since adding the second table it starting to become slower as 2148 rows were added.

Now I am only using 2 tables worth of data (first table with 680 rows, second table with 2148 rows) and when running a wider search criteria rather than very specific search it takes around 30 seconds to complete. Considering this is only 2 tables, I believe that when all 29 tables are added it could take 10+ minutes to complete since these two tables are quite small compared to others (Biggest table will have around 8000 rows).

Below is an example of the tables. Please see the example excel below for a better view of more rows.

Example Table

SKU, Count and Size aren't filled in since these are unimportant to the search, just copy and pasted where the Manufacturer, Brand, Sub Brand and Flavour match.

This is a snippet of the search function:

For k = 0 To 28

    For v = 1 To countryLastRow(k)
        If Sheet3.Cells(v + 3, countryRanges(k) + 3) = Sheet1.Range("C6") Then
             Sheet3.Cells(v + 3, countryRanges(k)).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) - 1)          'Manufacturer
             Sheet3.Cells(v + 3, countryRanges(k) + 1).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k))          'Brand
             Sheet3.Cells(v + 3, countryRanges(k) + 2).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 1)      'SubBrand
             Sheet3.Cells(v + 3, countryRanges(k) + 3).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 2)      'Flavour
             Sheet3.Cells(v + 3, countryRanges(k) + 4).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 3)      'SKU
             Sheet3.Cells(v + 3, countryRanges(k) + 5).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 4)      'Count
             Sheet3.Cells(v + 3, countryRanges(k) + 6).Copy Destination:=tbl.DataBodyRange(v, countryRanges(k) + 5)      'Size
        End If
    Next v
Next k

where

  • K = 0 to 28 scrolls through the country table columns

  • v = 1 to countryLastRow(k) scrolls through the rows for the country table

  • countryLastRow(k) is the number of rows in the table on sheet3 so it won't just search through loads of blanks if the max number is more than the current table contains.

  • countryRanges(k) is an array holding the starting column number for each table

This snippet of code is 1 of 16 versions as there are multiple ways to define the criteria (used in a select case function) where it finds all cells in column 1 of a country table which matches the criteria of a ComboBox box on Sheet1 and the rest copy/pastes it.

If the ComboBox on Sheet1 said WEETABIX LTD, All Brands, All Sub Brands, All flavours then it would copy both WEETABIX rows from AE Table and both WEETABIX rows from BE table leaving out the Kelloggs rows.

I tried to change the way my search worked, attempted to only look in 1 column at a time rather than all rows in 1 column (for table1), next row then when ended going to next table however this was actually slower by a few seconds when I timed it.

I've got an idea to do some data manipulation upon entering the data into tables (which uses a code to extract data from external Excel workbooks. So it is all alphabetical and find the first cell and last cell with the criteria and do a .range.copy .pastespecial but will take a while to write the code to do this as it will require multiple levels of sorting.

Another Idea, where the condition is true, get the column1 value and .copy .pastespecial of the range using .offset to get the whole row rather than .copy destination each cell in the row

Full Code and excel table example links below

Example table in excel link here: http://www.filedropper.com/showdownload.php/example_6

Full code in pastebin here: http://pastebin.com/WpeE84fX

If you want access to the full document let me know and I will send it privately.

  • 1
    please consider editing to make the question self-contained. Some readers won't go to filedropper and pastebin links. Also, sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. See How to Ask – gnat Jan 30 '15 at 9:15
  • Thanks gnat. Will give it a re-do. I don't know how to add 5 column type table into stack exchange so thought a filedropper would be easier plus my code is very long so again thought paste bin would be nicer to read. – Matt Jan 30 '15 at 9:27
  • asciiflow to show tables? As for large code dumps, these aren't welcome neither at SO nor here - askers are expected to make sensible snippets or MVCE – gnat Jan 30 '15 at 9:42
  • Will do an asciiflow table now, I've snipped just the important code. I've just seen when people only post snippets that everyone always just asks for the full code. Wont be long now – Matt Jan 30 '15 at 9:49
  • It also helps if you tell us (somewhere at the start) that this is an Excel issue – Jan Doggen Jan 30 '15 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.