Delete rows in Excel [VB]
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?
Re: Delete rows in Excel [VB]
Hi,
something like this should work:
Quote:
- Sub test ()
- Const LIGNE_DEBUT As Long = 1
- Const LIGNE_FIN As Long = 6
- Const COLONNE_DEBUT As Long = 1
- Const COLONNE_FIN As Long = 3
- Sunday supprime_ligne As Boolean
- Sunday Line As Long
- Sunday column As Long
- Sunday nb_suppr As Long
- nb_suppr = 0
- we loop on lines
- For row = LIGNE_DEBUT To LIGNE_FIN
- 'default this line is not deleted
- supprime_ligne = False
- we loop over the columns
- For column = COLONNE_DEBUT To COLONNE_FIN
- Next line
- 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.
Re: Delete rows in Excel [VB]
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