Deleting rows if cell is empty

[benlawton]

Hi All

I have found various similar threads on here however none of the solutions actually work for me, or rather I cannot make them work for me, that is.

Basically I have a sheet with a quotation table in it. In column C is a drop down list which automatically populates the model no. and price columns as per a VLOOKUP, which works great.

There are 14 lines on the quote table, as there are 14 possible products. Once a quote has been completed I would like to delete the rows which have not had a selection made from the drop down validation box. I only want the code to run in the range C57:C70.

As an addition there are pages of text after the page which this quote table is on, and for every line which is removed I would like a line added a the bottom of the page, row 79, in order to keep these pages in alignment for printing.

I have tried various peices of code and played around with them and the nearest I have managed to get is as follows:

Please Login or Register  to view this content.

However this deletes the rows within the range which DO have something in column C rather than empty cells and I cannot work out why.

Please help!

Many thanks

Ben

[Special-K]

Works if you change column 4 to column 3 (column 4 is D column not C anyway)

Please Login or Register  to view this content.

[mudraker]

Special-K

Please take a couple of minutes and read ALL theForum Rules then wrap your VBA code (Rule 3)

[benlawton]

Works if you change column 4 to column 3 (column 4 is D column not C anyway)

If IsEmpty(Cells(i, 3)) Then Rows(i).Delete

Hi Special K

Thanks for you reply! This is what I mean about iot being me not able to make it work!

I was literally just coming back to post that I had found a solution which seems to work well and is very simple, as follows:

Please Login or Register  to view this content.

I have recorded a macro which gives me the code i need to insert the new row at Row 79. I have tried adding it to the end of my code however it only adds in one row. I will need one row inserted for every row which is deleted. I am not sure what would be the best way to go about this, I presume the options include delete one - add one - delete one - add one etc. or getting excel to count the number of empty rows prior to the delete operation and adding in the number of rows, either before or after the delete operation.

Any advice would be much appreciated!

Ben