Forum Discussion

ASB_98Brazil's avatar
ASB_98Brazil
Copper Contributor
Dec 07, 2018

Macro VBA

How to exclude intervals with 10, 11, 13, 15, 16 and 17 consecutive lines, repeated several times in a column, where the first line always starts with "TOTAL ACCUMULATED *" and the last one always ends with "INSS Base * ", Via a macro vba loop?

Macros and VBA

13 Replies

Sort By
  • erol sinan zorlu's avatar
    erol sinan zorlu
    Iron Contributor
    Dec 07, 2018

    you can define an array of intervals and use it for reference:

    dim arrInterval as variant

    arrInterval=array(10,11,13,15,16,17)

     

    dim NoOfLoop as integer, NoOfInterval as integer

     

    for NoOfLoop=0 to 3

           for NoOfInterval=Lbound(arrInterval) to ubound(arrInterval)

                    .....

                    your code here

                   .....

            next NoOfInterval

    next NoOfLoop

    • ASB_98Brazil's avatar
      ASB_98Brazil
      Copper Contributor
      Dec 08, 2018

       

      Good morning mate,

I am a beginner in VBA. I was unable to edit the code to exclude line breaks that start with "TOTAL ACUMULADO DAS *" and end with "Base INSS: *", for example "A18: A28" and "A50: A60" with 11 lines to be A1412: A1426 with 15 rows to be deleted, "A2084: a2100" with 17 rows to be deleted, and "A2729: A2740" with 12 rows to be deleted. I've attached the example worksheet so you can mirror it. The array would stay (11, 12, 13, 15, 17).

       

      • erol sinan zorlu's avatar
        erol sinan zorlu
        Iron Contributor
        Dec 09, 2018

        hello,

        what you can do is to create a two dimensional array where the first value is the string you search and the second one is the number of cells to be eliminated. then with a for loop you need to search in the first column the string part and then delete the number of rows that is in the same array.

         

        finding the value can be done with excel built in functions like find or findall

Resources