Beneindias
Board Regular
- Joined
- Jun 21, 2022
- Messages
- 120
- Office Version
- 365
- Platform
- Windows
- MacOS
Hello, excel gurus,
So, I have a vba code that checks every checkbox that i have in a table, and deletes said checkbox if the row is empty or outside of the table, but I had to put a "+8" in the row range, because the table starts at row 7 of my worksheet.
Problem with this, is if someone make any change in this sheet and had or delete a row in before the table, I think that this code will not work very well.
This is my code:
Can I make any change in this code, to turn it completely dinamic according to the table?
Thank you all
So, I have a vba code that checks every checkbox that i have in a table, and deletes said checkbox if the row is empty or outside of the table, but I had to put a "+8" in the row range, because the table starts at row 7 of my worksheet.
Problem with this, is if someone make any change in this sheet and had or delete a row in before the table, I think that this code will not work very well.
This is my code:
VBA Code:
Dim PagoColumn As String: PagoColumn = "B"
Set PagoColumn = "B"
With Sh
'Número da última linha da tabela
lastRow = WorksheetFunction.countA(Range(LastRowColumn))
End With
For Each Cbx In ActiveSheet.CheckBoxes
'Checa se o espaço não está ocupado por uma checkbox. "lastRow + 8" para que seja considerada a linha após o fim da tabela
If Not Intersect(Cbx.TopLeftCell, ActiveSheet.Range(PagoColumn & lastRow + 8)) Is Nothing Then
'Apaga a Checkbox, se esta estiver na linha após a tabela
Cbx.Delete
'No caso de não haver nenhuma checkbox após a tabela, este próximo código checa se a checkbox tem ligação a alguma célula
'(quando a linha é apagada, a checkbox perde a ligação à célula e fica com erro #REF!)
ElseIf Cbx.LinkedCell = "#REF!" Then
'Apaga a checkbox se esta não tiver nenhuma célula ligada
Cbx.Delete
End If
'Passa para a próxima checkbox
Next Cbx
Can I make any change in this code, to turn it completely dinamic according to the table?
Thank you all