Excel - Macro to delete a row

[Jase]

Wondering if anyone can help on a macro to do the following:

I get a list with various information on maybe a a few hundred rows by approx. 20 columns, however some rows are not applicable as certain cells will have no information.

I would like to do either of the following:

create a macro that will automatically delete all rows where a specific cell has no data (is empty)

so for instance if D4, D10, D14 are empty then the entire row will be deleted.

That sounds like it may be doable?

As an extra and this would be the preferred option:

Instead of doing above (or as well as) create a list on a new tab that is made up of only the valid rows therefore I could still have the original list, and a list with only relevant information on another tab?

Thanks

 

[wesimmo]

You dont need a macro for the second option.

Put 2 helper columns on your original data.

The first is a check to see if the cells are empty or not.

The second increments a counter if the row is valid.

Then on the second sheet, put an index column in and then pull back the info from the original table by linking the index to the counter help column.

Edit: this assumes you're ok with simple IF statements and a lookup or 2.

 

[Jase]

You dont need a macro for the second option.

Put 2 helper columns on your original data.

The first is a check to see if the cells are empty or not.

The second increments a counter if the row is valid.

Then on the second sheet, put an index column in and then pull back the info from the original table by linking the index to the counter help column.

Edit: this assumes you're ok with simple IF statements and a lookup or 2.

Thanks, not exactly sure how those work but it gives me an idea on where to look/route to take

 

[wesimmo]

Ah ok.

What you'll need to do is put the helper columns to the left of the data for ease later.

Then your check column will be something like =AND(E2="",F2 ="") if you wanted to check multiple things.

That will return a True or False in column A.

Then in the next column, =IF(A2 = False, iferror( max($B$1:B1)+1,1),"")

That will increment the number only on the rows that have TRUE.

Then on the second sheet, put the numbers 1- whatever in column A and then a VLOOKUP to return the data from the other sheet.

A good tip here is to put your headers in row 2 and then in row 1 above the header put what column that field is in the other sheet.

Then your vlookup is dynamic and you can drag it if you refer to the cell in row 1 rather than putting the column to be returned as a fixed value.

 

[Jase]

Ah ok.

What you'll need to do is put the helper columns to the left of the data for ease later.

Then your check column will be something like =AND(E2="",F2 ="") if you wanted to check multiple things.

That will return a True or False in column A.

Then in the next column, =IF(A2 = False, iferror( max($B$1:B1)+1,1),"")

That will increment the number only on the rows that have TRUE.

Then on the second sheet, put the numbers 1- whatever in column A and then a VLOOKUP to return the data from the other sheet.

A good tip here is to put your headers in row 2 and then in row 1 above the header put what column that field is in the other sheet.

Then your vlookup is dynamic and you can drag it if you refer to the cell in row 1 rather than putting the column to be returned as a fixed value.

Thanks again, I'll give that a go.

 

[Mekrel]

Quickest way of doing this is using advanced filter, you can set it to advanced filter to a new tab so your source data remains the same.

Use <>"" for your filter criteria

 

[wesimmo]

Quickest way of doing this is using advanced filter, you can set it to advanced filter to a new tab so your source data remains the same.

Use <>"" for your filter criteria

Ha this might be easier.

I need to play with new features more and not just rely on my old ways!

 

[Jase]

Quickest way of doing this is using advanced filter, you can set it to advanced filter to a new tab so your source data remains the same.

Use <>"" for your filter criteria

That is super handy thanks (and works) but a little too "manual" for what I was looking for in this instance as you have to manually select the data and amend the advanced filter each time.

 

[Grrrrr]

If you still want code, this'll do it:

Sub DeleteRowsWithBlanks()

    ActiveSheet.Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

Just change the range "A:A" to whatever you need.

 

[Grrrrr]

And if you want a dynamic solution (requires the latest version of Excel), try this formula:

=FILTER(A1:Z100,NOT(ISBLANK(A1:A100)))

Where A1:Z100 should be the range of your table and A1:A100 is the column with the blanks.