Delete rows in Excel [VB]

[Janet J]

Sponsored Links

I got a list of data in the excel sheet and would like to create a macro that whenever i sees shows an empty cell in a matrix, I delete all the rows of a table containing one or more empty cells in column A to column P. I am beginner and I can not.

Thank you in advance for your help?

[Adley]

Hi,

something like this should work:

1. Sub test ()
2. Const LIGNE_DEBUT As Long = 1
3. Const LIGNE_FIN As Long = 6
4. Const COLONNE_DEBUT As Long = 1
5. Const COLONNE_FIN As Long = 3
6. Sunday supprime_ligne As Boolean
7. Sunday Line As Long
8. Sunday column As Long
9. Sunday nb_suppr As Long
10. nb_suppr = 0
11. we loop on lines
12. For row = LIGNE_DEBUT To LIGNE_FIN
13. 'default this line is not deleted
14. supprime_ligne = False
15. we loop over the columns
16. For column = COLONNE_DEBUT To COLONNE_FIN
17. Next line
18. End Sub

Try to make constants from the beginning and that's all.
In your case, if you want to go up the column P COLONNE_FIN must apply 16.

It is probably not very clean but it works.

[Mecurtis]

Hi

Here I will suggest some regarding your coding

I would add just beginning to code a
Application.ScreenUpdating = False
then at the end of the code a
Application.ScreenUpdating = True

You hide what excel, and therefore can save time at the execution.

In addition, the line 27 does nothing, or spend time at the execution.
Same for lines 36-37, a
Rows (line & ":" & row). Delete Shift: = xlUp

If after the last is not a great thing (as the previous If only validate a test on the contents of the cell), but it is not the concern, it works.

Best regards