+ Reply to Thread
Results 1 to 8 of 8

Deleting ROWS via VBA...Excel 2010

  1. #1
    Registered User
    Join Date
    11-19-2013
    Location
    Frankfort, OH, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Deleting ROWS via VBA...Excel 2010

    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

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Deleting ROWS via VBA...Excel 2010

    Skip a row and add another line of valid data to see if you get the same error, it looks like specialcells(4) is returning 0 cells because they're not part of the usedrange.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Deleting ROWS via VBA...Excel 2010

    You should have visited this site:-

    http://www.ozgrid.com/VBA/VBACode.htm


    Replace your subroutine with this:-

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-19-2013
    Location
    Frankfort, OH, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Deleting ROWS via VBA...Excel 2010

    Thanks, Solus. My test data are spread from F2 to F12, and I'm still getting the same runtime error.

  5. #5
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Deleting ROWS via VBA...Excel 2010

    Can you attach your workbook so we can check it out?

  6. #6
    Registered User
    Join Date
    11-19-2013
    Location
    Frankfort, OH, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Deleting ROWS via VBA...Excel 2010

    Mehmetcik, thank you for the response.
    I pasted your code as written, and got the same error message. Digging a bit deeper, I'm wondering if there is an issue with the syntax somewhere.

    I rewrote the code as follows and it works. I had to make a change to the formulae in the source document to force the '0' into Column F (using an IFERROR wrapper) in the absence of valid data (formerly is was leaving it blank, or so I thought). Here is the code that now works:

    Dim i As Long


    Range("F2:F601").Select

    With Application

    .Calculation = xlCalculationManual
    .ScreenUpdating = False

    For i = Selection.Rows.Count To 1 Step -1

    If Selection.Rows(i) = 0 Then

    Selection.Rows(i).EntireRow.Delete

    End If

    Next i



    .Calculation = xlCalculationAutomatic

    .ScreenUpdating = True

    End With

    End Sub

  7. #7
    Registered User
    Join Date
    11-19-2013
    Location
    Frankfort, OH, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Deleting ROWS via VBA...Excel 2010

    File is over 20MB, Solus.

    I have a 'fix' now in place, but I'm really wondering about the syntax of my first delrows(): Range("F2:F601").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    I'll have to do a bit more reading, I think.

  8. #8
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Deleting ROWS via VBA...Excel 2010

    Its because the cells aren't blank. They have a formula. If you have a working fix then instead I would do more reading on the syntax you used for it. Better to improve on a working solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 06-07-2018, 04:16 PM
  2. Replies: 2
    Last Post: 03-16-2016, 03:16 PM
  3. [SOLVED] excel 2010--need formula for deleting row if a specific cell in that row = 0
    By guyel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-14-2013, 01:02 AM
  4. Deleting menu items in Excel 2010
    By andrefrancis1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-04-2013, 07:02 PM
  5. Deleting Rows Excel 2010
    By extrapulp in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-27-2010, 08:51 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1