Results 1 to 4 of 4
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Code deleting wrong row in excel

    I have the following code that checks a selected spreadsheet for blank rows of data before it does any manipulation of the cells and then importing the data into my table in access. It works great except for the very first line in the spreadsheet that has merged cells in columns A-I. It keeps deleting this row. I am not understanding where to modify my code to get it to stop this. It took me all day to get this far. Thank you for your help in advance.

    -Walker



    Code:
    Public Sub DeleteBlankRows(xls As Object) 'xls is active worksheet being modified from other subroutine
    
    Dim wks As Worksheet
    Dim lngLastRow As Long, lngLastCol As Long, lngIdx As Long, _
        lngColCounter As Long
    Dim blnAllBlank As Boolean
    
    Set wks = xls
    
    With wks
        'Now that our sheet is defined, we'll find the last row and last column
        lngLastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, _
                                 SearchOrder:=xlByRows, _
                                 SearchDirection:=xlPrevious).Row
        lngLastCol = .Cells.Find(What:="*", LookIn:=xlFormulas, _
                                 SearchOrder:=xlByColumns, _
                                 SearchDirection:=xlPrevious).Column
    
        'Since we need to delete rows, we start from the bottom and move up
        For lngIdx = lngLastRow To 1 Step -1
    
            'Start by setting a flag to immediately stop checking
            'if a cell is NOT blank and initializing the column counter
            blnAllBlank = True
            lngColCounter = 2
    
            'Check cells from left to right while the flag is True
            'and the we are within the farthest-right column
            While blnAllBlank And lngColCounter <= lngLastCol
    
                'If the cell is NOT blank, trip the flag and exit the loop
                If .Cells(lngIdx, lngColCounter) <> "" Then
                    blnAllBlank = False
                Else
                    lngColCounter = lngColCounter + 1
                End If
    
            Wend
    
            'Delete the row if the blnBlank variable is True
            If blnAllBlank Then
                .Rows(lngIdx).Delete
            End If
    
        Next lngIdx
    End With
    
    
    'MsgBox "Blank rows have been deleted."
    
     End Sub

  2. #2
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    My goal here is to remove blank rows from the spreadsheet before I import it. The reason for this is the manufacturer that sends me the data in a spreadsheet has messed up before and had about 10 blank rows between the headers and the rest of the data. If there is a better way to extract the good data from the spreadsheet please advise me on a direction to go. I am trying to make it so my database gets the data into the correct places no matter what the manufacturer sends me. Thank you in advance. --Walker

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I presume you are using transferspreadsheet to append directly to a table - consider instead to use it to create a linked table instead. Then you can simply filter out the blank rows in your append query to your destination table.

  4. #4
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Thats a great idea. thank you Ajax. I will try that.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Maybe my code is wrong somewhere?
    By lccrews in forum Access
    Replies: 14
    Last Post: 02-09-2018, 04:47 PM
  2. What is wrong with this code?
    By Lou_Reed in forum Access
    Replies: 9
    Last Post: 12-20-2017, 02:17 PM
  3. What is wrong with this code
    By MarkA70 in forum Programming
    Replies: 2
    Last Post: 02-13-2016, 06:19 PM
  4. Deleting the wrong duplicate
    By mattzkn in forum Access
    Replies: 1
    Last Post: 12-10-2013, 06:42 AM
  5. What's wrong with my code?
    By Dalagrath in forum Forms
    Replies: 11
    Last Post: 05-18-2011, 04:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums