Please pardon the long post...
Running Windows7, MS_Office2010.
I've reviewed/tried a dozen or so suggestions from the forum for this issue, but none have worked. I have a multi-tabbed spreadsheet used for data collection by a staff of field technicians. Once their data is collected, it must be further processed to create two new CSV files (to load to client system) and one new XLS file (for later report generation).
My issue is the second CSV file. There is a maximum of 600 rows of collected data from the field; usually well short of that number. I have built a tab ("Working Tab") in the field workbook where the technicians record their collected data. That feeds another tab ("Submittal") which is the format needed for upload to the client system. However, in the course of the field data collection, there can be a number of rows (no particular order) which will NOT get picked up in the "Submittal" tab, but will instead leave a row that cannot be uploaded to the client's system.
My process design is to pull the complete set of possible rows (601, with header row) from the "Submittal" tab. Then, create a new workbook and PasteSpecial (Values and Number Formats, only). Then, I need to delete all rows in which Column F is blank/empty. Due to other design constraints, I cannot sort the "Working Tab" data (other functions on other tabs).
Because I have to have the appropriate data in a CSV to upload to the client system, and I cannot have blank lines, I have the following two Macros; Macro2() and delrows(). Without the delrows() call (commented out), macro2() runs fine and creates the CSV file (sortation is correct), but it (of course) still has the blank lines I need to remove.
WITH the delrows() call, I get a runtime error 1004, "no cells found", and debugging goes directly to the delrows() function. My test data has six rows of valid data (an entry in column F), so there should be 594 rows deleted and seven remaining (header row and six data rows). That is not happening. I still have all 600 rows of data (confirmed by a 'flag' in a different column).

Can't post the whole workbook as it runs 20MB...

Sub Macro2()
'
' Macro2 Macro
' To create the DOI.CSV file from the CAPTURE file
' Storage to be into an established directory c:\RAN\ProcessFiles\ per this Macro

'
Dim file1Name As String
Dim file2Name As String
Sheets("Site Details").Select
file2Name = Worksheets("Site Details").Range("E23") 'To create file name
file1Name = "C:\RAN\ProcessFiles\" + file2Name 'To determine storage path
Sheets("Submittal").Select 'Navigate to correct worksheet
Rows("2:602").Select 'Select data range
Selection.Copy
Workbooks.Add 'Creates new workbook
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
Call delrows 'Need to delete all rows in which column F is blank or empty
'Following sorts the data in the new worksheet
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F2:F601") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:BE601")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'To save the new worksheet as csv for client system upload
ActiveWorkbook.SaveAs Filename:= _
file1Name, FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub


Sub delrows()

Range("F2:F601").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub