VBA to paste to new sheets if column is blank
Hi there, I wondered if anyone could help me with this problem?
I have a list of data in columns C, D, E & F like as below:
Apple 4.5 USD 12/07/2015
Banana 5.1 CAD 01/08/2015
Orange GBP 13/02/2015
Grape 10020 USD
Cherry 1.009 CNY 10/10/2014
Sometimes, column D or F are empty (ie. they don't contain a price or a date). I need a macro which would look for those with nothing in column D and/or nothing in column F - cut this entire row and paste it into a new sheet. The range of data I'm working with would change each day.
Also (if possible) where rows have been cut and pasted to the new sheet, I'd like the data to automatically shift up rather than just leaving a blank row on the original spreadsheet? Is this possible? Please let me know if you need any further information at all!!
1 Attachment(s)
Hi all please find the attachment for your assistance or follow below mentioned code.
Sub Move_Record()
Dim rng As Range
Dim wksht As Worksheet
Set rng = Sheets("Data").Range("C1").CurrentRegion
Set wksht = Worksheets.Add
Sheets("Data").Activate
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Copy
wksht.Paste
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Sheets("Data").Range("C1").Select
End Sub