0

I'm trying to improve the speed with which I find data, copy a row, paste into a different sheet, and replace a value between multiple sheets.

Currently, I am activating each sheet and using cells.Find, but as it's searching through 60k+ rows, this goes extremely slowly or not at all.

Here is the basic process that I"m trying to improve:

Sub UpdateSKU()
'On Error GoTo ErrorCatch


Dim OldSKU As Long
Dim NewSKU As Long
Dim SKUSubset As String
Dim SubsetRange As Range

OldSKU = Sheets("Rollover Request").Range("A2")
NewSKU = Sheets("Rollover Request").Range("B2")


'UPDATE NEW SKU IMPORTER
 Sheets("SKU Exporter").Activate

Cells.Find(what:=OldSKU, after:=ActiveCell, LookIn:=xlFormulas, _
    Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Copy
Sheets("New SKU Importer").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Worksheets("New SKU Importer").Columns("A:A").Replace what:=OldSKU, Replacement:=NewSKU, Lookat:=xlPart _
    , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


End Sub

I do this for multiple different sheets in the workbook.

Any help would be greatly appreciated! Thanks!

Allison Atwill
  • 33
  • 6
  • [This post covers a lot of things](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), so read and try to adapt to it. Also turn on and off *screenupdating*; `Application.ScreenUpdating = False` after the variable declarations and `Application.ScreenUpdating = True` before you end sub. – L42 Nov 14 '14 at 02:51
  • There is almost never (and not in your code) any reason to Activate (or Select) ranges or worksheets. And doing so will slow your code significantly. Just refer to the ranges directly. Also, be sure to turn off ScreenUpdating; set Calculation to manual; and, if you have event macros running, disable events. – Ron Rosenfeld Nov 14 '14 at 02:53
  • Hi, I turned off screen updating so that did help. I'm super new to VBA so referring to the ranges directly is what I'm trying to figure out how to do :) – Allison Atwill Nov 14 '14 at 19:08

3 Answers3

0

I'm not sure wether I got your problem right. Are you looking for a particular value in the whole source-sheet and whenever one single occurence shows up, you paste the column in which you found the value to the target sheet? Is that value showing up only once in the whole sheet, or several times in one single column?

Well, anyways, here is an idea how to speed up at least the search and copy part of your task. You will find that using arrays will make such processes incredibly fast.

Hope this helps...

Sub SF_row()

Dim arr_DB As Variant
Dim i As Long, j As Long
Dim no_col As Integer, no_rows As Long
Dim col_no As Integer

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("SKU Exporter")
Set ws2 = ThisWorkbook.Worksheets("New SKU Importer")
ws1.Activate
no_rows = ws1.Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row
no_cols = ws1.Range(Cells(1, Columns.Count), Cells(1, Columns.Count)) _
    .End(xlToLeft).Column
arr_DB = ws1.Range(Cells(1, 1), Cells(no_rows, no_cols))
For i = 1 To no_rows
    For j = 1 To no_cols
        If InStr(arr_DB(i, j), "String or Value you search for") <> 0 Then
            col_no = j
        GoTo copy_column
        End If
    Next
Next
copy_column:
ws1.Range(Columns(col_no), Columns(col_no)).Select

With Selection
    .Copy
End With
ws2.Activate

ws2.Range("A1").Select
ActiveSheet.Paste
End Sub
L42
  • 19,427
  • 11
  • 44
  • 68
jemmy
  • 60
  • 1
  • 6
  • @L42: I'm new here. What did you change in my answer? Can I improve the way I use the forum? – jemmy Nov 14 '14 at 08:54
  • Hi welcome. I didn't change it in any way, just improved readability and remove run code snippet since I don't think it is applicable. you can revert it back to how it was anytime if you're not satisfied. – L42 Nov 14 '14 at 09:01
  • Hi @jemmy, thanks for this! Unfortunately, I'm getting a 1004 (Appliction-defined of object-defined error) at this part: skuExp.Range(Columns(col_no), Columns(col_no)).Select Also, based on the advice in the thread mentioned above, I'm trying to avoid using .Select as much as possible. Any ideas on how to copy the row without using .Select? – Allison Atwill Nov 14 '14 at 19:32
  • Hi, apologies, I forgot to mention that you have to replace "String or Value you search for" with the value you are actually looking for. There is no error handling in that code, hence if the value is not found, there is simply an error message from VBA. Hope this helps... – jemmy Nov 14 '14 at 20:20
0

Using find is slow with more than a few thousand records. In some cases I've been able to filter or sort data to reduce the number of records being searched, but that depends on the exact data you're working with.

If some of the SKUs you're looking at are unchanged, it would be a little faster to only run your code on those that you know have changed.

rkwadd
  • 148
  • 6
0

So I didn't answer my question so much as find a workaround. Instead of finding the OLDSKU value, I decided to just filter for it, since there will only be one instance of it in each sheet.

Code looks like this:

Sub UpdateSKU()

Application.ScreenUpdating = False

Dim OldSKU As Long
Dim NewSKU As Long
Dim skuExp As Worksheet, skuImp As Worksheet
Set skuExp = Sheets("SKU Exporter")
Set skuImp = Sheets("New SKU Importer")

OldSKU = Sheets("Rollover Request").Range("A2")
NewSKU = Sheets("Rollover Request").Range("B2")



'UPDATE SKU IMPORTER

skuExp.Range("A1").AutoFilter _
                               Field:=1, Criteria1:=OldSKU

skuExp.Range(skuExp.Cells(2, 1), skuExp.UsedRange. _
                   SpecialCells(xlLastCell)).Copy

skuImp.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial _
       Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False

skuImp.Columns("A:A").Replace What:=OldSKU, Replacement:=NewSKU, LookAt:=xlPart _

skuExp.ShowAllData

End Sub

Thank you for all your help!

Allison Atwill
  • 33
  • 6