Forum Discussion

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 en...

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
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).
Modelo Exclusão Intervalo Linhas.xlsm65 KB
Planilha Modelo Exclusão Linhas.pdf645 KB
- 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
  - ASB_98Brazil
    Copper Contributor
    Dec 09, 2018
    Dear friend,
    I'm here in Teresina-PI Brazil, I'm a beginner in VBA Excel. As I attached the spreadsheet template, could you please edit all the VBa code in the macro for me? I'd be very grateful for that kindness.

Resources