Dec 07 2018 10:53 AM
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?
Dec 07 2018 01:06 PM - edited Dec 07 2018 01:08 PM
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
Dec 08 2018 04:19 AM
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).
Dec 08 2018 11:05 PM
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
Dec 09 2018 03:30 AM
Dec 09 2018 11:55 PM
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.
Dec 10 2018 08:51 AM
OK! Friend,
I will await your response. And thank you for your attention.
Dec 10 2018 10:26 AM
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
Dec 11 2018 10:10 AM
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.
Dec 11 2018 10:15 AM
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.
Dec 11 2018 01:16 PM
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.
Dec 12 2018 03:10 AM
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...
Dec 15 2018 03:38 AM
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.
Dec 17 2018 04:34 AM
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.