"For" but only visible cells issue

[KasperC]

Hello,

I'm trying to remove and change certain values from a dataset which has been filtered.
As the size of the data-set is quite big, I want to narrow down the FOR range in order to make the vba more efficient.

I found some threads suggesting the "For Each" and with a range In specialcells "type visible" - but I seem to have issiues to get this to work.
In addition, As I'm deleting and moving up rows from the data-set, I'm afraid that the code will skip the just moved-up row if i leave the code "as is".

This is what my code looked like, but as it checks every i, its very inefficient - even though it does work perfectly.

        For i = LastRow To 2 Step -1
            If Left(ws.Cells(i, 1), 1) = "2" Then
                    If Left(ws.Cells(i, 1), 4) = "2000" And Len(ws.Cells(i, 1)) > 4 _
                        Then
                            ws.Cells(i, 1).EntireRow.Delete Shift:=xlUp
                        End If
                    If Left(ws.Cells(i, 1), 2) = "23" And Len(ws.Cells(i, 1)) > 4 And Len(ws.Cells(i, 1)) < 13 _
                        Then
                            ws2.Range("A2").Value = ws.Cells(i, 1).Value & "0000"
                            ws.Cells(i, 1).Value = ws2.Range("H2").Value
                        End If
                    If Left(ws.Cells(i, 1), 2) = "20" And Len(ws.Cells(i, 1)) > 4 And Len(ws.Cells(i, 1)) < 13 _
                        Then
                            ws2.Range("A2").Value = ws.Cells(i, 1).Value & "0000"
                            ws.Cells(i, 1).Value = ws2.Range("H2").Value
                        End If
                End If
        Next i

I suppose something like this is what I need, allthough I'm not able to make it work.. Does anyone have any ideas?

        Dim i As Range
        For Each i In ws.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
            If Left(i, 1) = "2" And Not Left(i, 2) = "29" Then
                    If Left(i, 4) = "2000" And Len(i) > 4 _
                        Then
                            i.EntireRow.Delete Shift:=xlUp
                        End If
                    If Left(i, 2) = "23" And Len(i.Value) > 4 And Len(i.Value) < 13 _
                        Then
                            ws2.Range("A2").Value = i.Value & "0000"
                            i.Value = ws2.Range("H2").Value
                        End If
                    If Left(i, 2) = "20" And Len(i.Value) > 4 And Len(i.Value) < 13 _
                        Then
                            ws2.Range("A2").Value = i.Value & "0000"
                            i.Value = ws2.Range("H2").Value
                        End If
                End If
        Next i

Thank you for your time.

Sincerely
Kasper C

 

[KasperC]

Does that mean that if the value in column A starts with 2000 but the date in that row in column C doesn't equal the L1 date then that row should not be deleted?

Well it does not matter really, as long as it does not enter the output sheet/wb. The entry data-set is generated and used for this purpose only - then its thrown away.
So I guess it depends on whatever quickest and easiest. Mabye one can select and copy over without deleting?

 

[kevin9999]

OK, like before, let's take this one step at a time. With the sample data you have provided, I ran the exact same code from post #4. The result was all but 11,077 rows remain. It took .035 seconds, and would be even quicker if you didn't have an entire column of volatile formulas in column D (the RandBetween formula).
You said previously:

But it seems to be deleting all values under 13 digits long in the A column, though - such as "4036" or "2056468"

There are 12 rows of 4036 remaining in column A. There are no rows of 2056468 in column A - which would have been difficult given that there weren't any there to start with!
You also said:

In addition, it does not seem to be deleting any rows with the array cases in column B

There are no rows at all where column B contains 0, 1, 4, or 9?!

So, could you please run the code from post #4 on the sample file and let me know if you get a different result to what I've just described?

 

[KasperC]

So, could you please run the code from post #4 on the sample file and let me know if you get a different result to what I've just described?

I got the same result as you now, all seems to be good.
Don't know why I got a different result last time - must have done something wrong.

And my mistake, this data-set did not contain any 20-values - But they should be handeled the same way as the 23-values. Such as 23578015

Also, the random-formulas was just for de-sensitivity purposes. I suppose there are better ways to generate random numbers that are not formulas.

 

[kevin9999]

OK next stage. I'm going to need some clarification on some of your next steps (one at a time)

- Format all 20/23 (under 13 dig) through Sheet2 (Input col A & "0000", output col H)

Does this mean:
You're referring to values starting with 20 or 23 (and 12 or less digits in length = "under 13") in column A in sheet2 (yes or no)

Turn these values into "20" or "23" PLUS "0000" (in other words, concatenate the first 2 digits with 0000 into a single value of 6 digits long) ignoring any values that don't start with these 2 values (20 or 23)?

Once the new concatenated value has been created, put that value into the same row in column H of sheet 2 (yes or no), and...
what do you want dome to that value in column A (delete the value, leave it untouched, something else?)

 

[KasperC]

OK next stage. I'm going to need some clarification on some of your next steps (one at a time)

Does this mean:
You're referring to values starting with 20 or 23 (and 12 or less digits in length = "under 13") in column A in sheet2 (yes or no)

Turn these values into "20" or "23" PLUS "0000" (in other words, concatenate the first 2 digits with 0000 into a single value of 6 digits long) ignoring any values that don't start with these 2 values (20 or 23)?

Once the new concatenated value has been created, put that value into the same row in column H of sheet 2 (yes or no), and...
what do you want dome to that value in column A (delete the value, leave it untouched, something else?)

First, yes.

Take the value, ex. 23578015 from A column in Sheet1, add "0000" and paste it into A2 (or any row) in Sheet2. Then replace the 23578015 (Sheet1, A) value with the value that appears in H2 -> in this case 02357801500009.

This "pasting into A2 and grabbing the value from H2" process should be done with all 23/20-values under 13 digits (none that are over 13 digits in this dataset, though). I guess the formatting of those numbers can be done in another way, but im unsure how to convert the calculations done in Sheet2 over into a vba-format.

 

[kevin9999]

OK, that's just confused me. I said:

You're referring to values starting with 20 or 23 (and 12 or less digits in length = "under 13") in column A in sheet2 (yes or no)

You said:

First, yes.

Then you said:

Take the value, ex. 23578015 from A column in Sheet1, add "0000" and paste it into A2 (or any row) in Sheet2.

Now you seem to be saying that it's the values in sheet 1 that need to be tested for starting with 20 or 23... Which is it?

 

[KasperC]

OK, that's just confused me. I said:

You said:

Then you said:

Now you seem to be saying that it's the values in sheet 1 that need to be tested for starting with 20 or 23... Which is it?

Oh thats my bad - Its the values starting with 20/23 in Sheet1 column A that needs to be pasted into the A column in Sheet2 with the added "0000".

Might help to see my previous code:

        For i = LastRow To 2 Step -1
            If Left(ws.Cells(i, 1), 4) = "2000" And Len(ws.Cells(i, 1)) > 4 _
                Then
                    ws.Cells(i, 1).EntireRow.Delete Shift:=xlUp
                Else
                    If Left(ws.Cells(i, 1), 2) = "23" And Len(ws.Cells(i, 1)) > 4 _
                        Then
                            ws.Cells(i, 1).Copy
                            ws2.Range("A2").PasteSpecial (xlPasteValues)
                            ws2.Range("A2").Value = ws2.Range("A2").Value & "0000"
                            ws.Cells(i, 1).Value = ws2.Range("H2").Value
                        Else
                            If Left(ws.Cells(i, 1), 2) = "20" And Len(ws.Cells(i, 1)) > 4 _
                                Then
                                    ws.Cells(i, 1).Copy
                                    ws2.Range("A2").PasteSpecial (xlPasteValues)
                                    ws2.Range("A2").Value = ws2.Range("A2").Value & "0000"
                                    ws.Cells(i, 1).Value = ws2.Range("H2").Value
                                End If
                            End If
                        End If
        Next i

 

[kevin9999]

Its the values starting with 20/23 in Sheet1 column A that needs to be pasted into the A column in Sheet2 with the added "0000".

Pasted where? In the same row it came from? Somewhere else?
And the value being pasted - if the value happens to be 23578015 in sheet 1, does it mean that 235780150000 is what's pasted into sheet 2?
And where do you get the value from to replace that value in sheet 1? From the same row it's on on sheet 2?

Also, what exactly are you trying to achieve with your concatenate formula in column H on sheet 2?

 

[KasperC]

The Sheet2 is made in order to convert and to create a "control-number" that goes to the end of the value.
I have to admit I don't know completely how this works nor the complete reasoning behind it - which is why I havent made any attempts at "coding it", other than to code the same processess which was done manually - which is to find these values (20/23), paste them into the A column and retrieve the output found in the corrosponding H-column.

The process is as follows, as youre describing it.
Find value in Sheet1, say 23578015 in A2068 (from the code-output you provided.)
Paste the value 235780150000 into A2 in Sheet2 (or any row).
Then copy the output value that appears in H2 Sheet2, in this case 0238095100006, and paste it back into A2068.

The core function of this is to "translate" the value from one systems "language" to anothers. The most important thing is that the "new" value from H2 Sheet2 is brought over to the end, Sheet3 with all the remaining data (and their "connected" row, ofcourse)

Did that make sense?

 

[kevin9999]

It's a bit clearer. What I don't understand is where you keep on saying:

into A2 in Sheet2 (or any row).

and the value that appears in H2 (for example if the value was pasted into A2 on sheet 2) is copied back to sheet 1 - replacing the 20 or 23 value. Column B of sheet 2 contains constant values - but columns C-H are formulas, so the row you paste the value into will give you a different value to paste back into sheet 1 depending on the row you paste it into on sheet 2 (because not all constants are the same). If I can get my head around that apparent anomaly of reasoning, I think I can move forward. But it won't be tonight as I need to give my brain a rest