Macro VBA

Copper Contributor

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?

13 Replies

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

 

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

 

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

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.

well I do not have a code that does a similar thing readily at hand. So I need to write it which takes some time. I cannot promise but I will try to write it in my free time.

OK! Friend,

 

I will await your response. And thank you for your attention.

hello,

below code finds the rows between TOTAL ACUMULADO and Base INSS

 

Option Explicit

 

Sub FindValuesDeleteRows()

Dim LastRow As Double
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Dim SheetSelection As Object

Dim RowNo As Double
For RowNo = 1 To LastRow

If InStr(1, Cells(RowNo, 1).Value, "TOTAL ACUMULADO", vbTextCompare) <> 0 Then

RowNo = RowNo - 1

Do

RowNo = RowNo + 1
Debug.Print RowNo & " - " & Cells(RowNo, 1).Value

Loop While InStr(1, Cells(RowNo, 1).Value, "Base INSS", vbTextCompare) = 0 And RowNo <= LastRow

End If

Next RowNo


End Sub

Friend,

When you run the macro from the last edited VBA code, nothing happens, and no error message is mentioned. What could be happening?
Another question: should the code be used along with the instructions of your first response? If true, how would the final edit of the final code cover the two instructions?
Thank you again for your understanding. Thank you very much.

hi,

code finds the rows between TOTAL ACUMULADO and Base INSS and writes the row number and cell value in immediate window. so you need to add your code to fit it to your needs. you can hide these rows. delete them what ever you want. add your code between Do... Loop While area.

Friend, I want to exclude all rows of ranges between "TOTAL ACCUMULATED" and "Base INSS", including those ends. As I already told you, I am a beginner in VBA macro, and frankly, I do not have enough knowledge to edit the required code. If you could help me, I would be very grateful.

Hello,

 

I haven't included the code to "exclude" as I do not fully understand what do you want to achieve by "excluding". secondly the code does find the cells you need to edit somehow, delete (at your own risk) or hide. I also told that the necessary code need to be added between Do.... Loop section. and the rest is a little bit googling about what you want to achieve. If you want to hide it you can, if you want to delete it keep in mind this is irreversible...

Hello Friend,

Here I am back. Well I tried, but I could not edit the statement to replace "debug.print" with one that deletes all the lines listed in the immediate display window. I know the process of deleting the lines is irreversible, but what I want is just that. You could, once again, help me in this task. I'll be very grateful.

 

to delete the entire row you need to use below code:

 

ROW(RowNo).EntireRow.Delete

 

put this in Do Loop area after the RowNo=RowNo+1

 

Keep in mind this code will delete all the rows including TOTAL ACUMULADO and Base INSS.