Is There An Add-In For That?

[abigailcrabb]

I'm working on a data entry project for work - credit card statements have been imported into Excel with 3 columns of information: Transaction Date(column A), Amount(column B), and the Merchant(column C). The spreadsheet contains 6 columns total; column E is automatically filled, depending on the credit card company. Two columns, by default, are left empty. Both for entry purposes; column D for Expense Description and column F to mark if the expense is Non-Deductible; in each row ONE OR THE OTHER should contain data, never both (unless for a memo regarding non-deductible items, then will use space in column D).

I'm getting to the end of the entry part and need to come up with a way to view, filter, extract, SOMETHING the rows that don't contain data in EITHER column D OR column F. If it weren't several Spreadsheets with several thousand cells, I would go through and copy/move each row myself. But with as many add-in's, helpers and tools as I've come across, I know there has GOT to be something to make this possible.. Please help? I just need a little direction and assistance with a reliable resource. Thanks in advance!

 

[Obied70]

If removing the entire row works you could use this code. Edit the range to suit your needs

Sub RemoveRows()

[D2:F5000].specialcells(xlcelltypeblanks).entirerow.delete

End Sub

 

[abigailcrabb]

If anything, I could copy the worksheet to a new one and delete what I don't need. It would be helpful then, although, I'm not familiar with code.. Is that a VBA format thing? I'm sure utilizing VBA would expedite the project, but it's above our knowledge range..

 

[abigailcrabb]

Also, thank you for your help!

 

[Obied70]

Now that I think about it, that won't work anyway. I'm just mindlessly posting things at this point sorry about that. The code posted above would remove the row if it found any blank cell, not a combination of cells.

 

[SpillerBD]

For filtering it either Auto-Filter (for a Range) or change the Range to a Table and use Slicers.
You can set the Filter to columns "D" & "F" both to Blank and then copy the range.
Another trick is to set the data to a Pivot Table so when you double click a value (Sum, Count etc) it generates a report from the original table the data that makes up that value.

The following Code will apply an Auto Filter to the range. The two lines of code are commented out but if allowed to run they would delete the rows meeting the filter criteria and then show all data by clearing the Filters.

Sub Macro1()
'
' Macro1 Macro
'
Dim MaxRow As Double
'
Range("A2").CurrentRegion.AutoFilter
MaxRow = ActiveSheet.Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row
ActiveSheet.Range("$A$1:$K$482026").AutoFilter Field:=4, Criteria1:="="
ActiveSheet.Range("$A$1:$K$482026").AutoFilter Field:=6, Criteria1:="="

'Rows("2:" & maxrow).Delete Shift:=xlUp
'ActiveSheet.ShowAllData
    
End Sub