How to solve the "program not responding error" when i extract data with same name in a column and put them into different worksheets?

Question

Based on the earlier thread that i posted, How do i extract merged data and put them into different worksheets? Everything seems fine until my program encounter the

"program not responding error"

which I think is due to using

Sheets(sheetname).UsedRange.Rows.count

(because of deleting and clearing rows) which cause this to happen. Based on other thread discussions, I tried to use this instead

Cells(Sheets("SheetName").Rows.Count, 1).End(xlup).Row

(better options as suggested by others) but it didnt give me the result that I want which is as shown below. So how do I modify my below code to solve the program not responding error in this case and still get the ideal scenario as shown below?

Put the extracted data into 3 different sheets namely index 1, index 2 and index 3 as shown below

   Sub UpdateVal()
    Static count As Long
    Dim iRow As Long
    Dim aRow As Long
    Dim a As Long
    Dim b As Long
    Dim selectRange As Range
    dim lastline as integer
    dim sheetname as string
    dim indexrowcount as integer
    dim wb as workbook
    dim ws as worksheet
    set wb= activeworkbook
    set ws=wb.sheets(Index)
    j = 2
    iRow = 1
    LastLine = ActiveSheet.UsedRange.Rows.count
    While iRow < LastLine + 1
        a = iRow + 1
        b = iRow + 17 ' Max Group Size with Same name in F to H column
        count = 1
        If ws.Cells(iRow, "F").Value = "Martin1" Then
            sheetname = "Index1"
        ElseIf ws.Cells(iRow, "F").Value = "John1" Then
            sheetname = "Index2"
        Else
            sheetname = "Index3"
        End If
        For aRow = a To b
            If ws.Cells(iRow, "F") = ws.Cells(aRow, "F") And ws.Cells(iRow, "G") = ws.Cells(aRow, "G") And ws.Cells(iRow, "H") = ws.Cells(aRow, "H") Then
                count = count + 1
            Else
                Set selectRange = Range("A" & iRow & ":J" & aRow - 1)
                selectRange.Copy
                indexrowcount = Sheets(sheetname).UsedRange.Rows.count
                Sheets(sheetname).Range("A" & indexrowcount).PasteSpecial xlPasteAll
                iRow = iRow + count
                Exit For
           End If
        Next aRow
    Wend

If Excel is not responding that means (in almost any case) that the VBA code is still running. Check your loops and how many times they run. Nested loops can easily run a very long time. What is the value of `LastLine` (so we can know how many times your loop will run)? — Pᴇʜ, Feb 10 '20 at 12:34
@Pᴇʜ i waited quite awhile for the program to run but it still shows no sign of responding...my value of lastline is 17 — cena, Feb 10 '20 at 12:43
Probably not **directly** solving your issue, but using [`Explicit`](https://stackoverflow.com/a/10717999/9758194) sheet references **will** at least help! Without looking in depth, maybe you are creating an endless loop. Stepping through your code using `F8` might help (keep a close eye on your variables). — JvdV, Feb 10 '20 at 12:43
@JvdV sorry, i am quite new to vba may i know what is that and how do i go about doing that? — cena, Feb 10 '20 at 12:45
I would step through line by line and check that the iRow increment is hit, otherwise it will be an infinite loop. — Nathan_Sav, Feb 10 '20 at 12:45
Technically this code works but is just that when i tried to rerun the code by deleting and pasting data again without exiting the workbook, the error will occur. — cena, Feb 10 '20 at 12:50
@cena see the answer posted. Your irow is only updated in one condition of the if statement, so could not be hit. — Nathan_Sav, Feb 10 '20 at 12:58
@cena If you *"waited quite awhile"* then you waited not long enough ;) But if your code is not solid it also can be that you have an endless loop. Which is more likely sicne your `LastLine` is only 17 it should be finishen in less than 1 min. Go through your code step by step using F8 and check the values of your variables in each step. So you see what actually happens (maybe some variables never increase or if statements have a different reslut than you thought). — Pᴇʜ, Feb 10 '20 at 13:23

score 3 · Accepted Answer · answered Feb 10 '20 at 12:50

3

My guess is that your Implicit sheet references could be the culprit. As mentioned in my comment, Implicit references will refer to the currently active sheet, or ActiveSheet.

Have a look at this post on how to avoid that. When you studied that post, you should have a Worksheet variables, for exmple ws to refer to.

Right now, If Cells(iRow, "F") = Cells(aRow, "F") And Cells(iRow, "G") = Cells(aRow, "G") And Cells(iRow, "H") = Cells(aRow, "H") Then could be TRUE on any ActiveSheet with all empty cells for example. That way you will never hit FALSE and iRow never gets incremented > Endless loop > Your error!

answered Feb 10 '20 at 12:50

JvdV

70,606
8
39
70

2

ah so i should be using Dim ws As Worksheet Dim wb As Workbook Set ws = Worksheets("Index") after that replace activesheet with ws? – cena Feb 10 '20 at 12:56
1

@cena, and then use it with `ws.Cells(x,y)` etc. – JvdV Feb 10 '20 at 13:01
but now when i did that, the error doesnt occur but the data are not pasted on index 1,index 2 and index 3 from the original data that is at index – cena Feb 10 '20 at 13:21
i updated my code but im not sure what else am i missing here after adding ws. and dim wb as workbook, dim ws=worksheets("index") – cena Feb 10 '20 at 13:25
@cena, looking at your code you haven't yet quite grasped the idea of setting a `Worksheet` variable correctly. You must assign a **valid** worksheet to the variable, right now I doubt your code would even compile since `Index` is an empty variable (and should not be used as a variable name in the first place) > `Set ws = wb.Worksheets("YourWorksheetName")` – JvdV Feb 10 '20 at 13:30

How to solve the "program not responding error" when i extract data with same name in a column and put them into different worksheets?

1 Answers1