none
will not execute after open of workbook and this is not an shift key issue RRS feed

  • Question

  • Hello, I am new to vba and i have searched for a solution,but can't seem to find one. I read about the shift key issue and i am not shifting keys just using f8, to step through the code. I took the advice to add a pause and that didn't work, nothing seems to work. if i put a stop after the open it will get pass the open,  but it then won't execute the rest of the loop. The open workbook will open the workbook and then go back to the 'set' or if i hit f5, it then goes back to the beginning of the program (sub) but it hasn't deleted the columns... thank you for your help. below is the code

    Option Explicit
    Sub DleteColumns()

    Dim objWorkbook As Workbook
    Dim i As Integer
    Dim keepColumn As Boolean
    Dim currentColumn As Integer
    Dim columnHeading As String
    Dim ws As Worksheet
     
    'This is temporary for testing this one below
     
    Application.DisplayAlerts = False
     
    currentColumn = 1
    'open the workbook with data
    DoEvents
    Set objWorkbook = Workbooks.Open( _
    "H:\C_Files\xls\a_C_Track_20171101.xls")
    'Do a pause
    Application.Wait (Now + TimeValue("0:00:10"))
    ThisWorkbook.Activate
    Set ws = ActiveSheet
    'Stop
    'read the data from the first columns
    For i = 1 To 1
     
    currentColumn = 1
        While currentColumn <= ActiveSheet.UsedRange.Columns.Count
            columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value
     
            'CHECK WHETHER TO KEEP THE COLUMN
            keepColumn = False
           
            If columnHeading = "reason" Then keepColumn = True
            If columnHeading = "first_name" Then keepColumn = True
            If columnHeading = "last_name" Then keepColumn = True
            If columnHeading = "employer_name" Then keepColumn = True
            If columnHeadimg = "city" Then keepColumn = True
            If columnHeading = "state" Then keepColumn = True
            If columnHeading = "date_of_birth" Then keepColumn = True
            If columnHeading = "ssn" Then keepColumn = True
           
            If keepColumn Then
            'IF YES THEN SKIP TO THE NEXT COLUMN,
                currentColumn = currentColumn + 1
            Else
            'IF NO DELETE THE COLUMN
                ActiveSheet.Columns(currentColumn).Delete
            End If
     
            'LASTLY AN ESCAPE IN CASE THE SHEET HAS NO COLUMNS LEFT
            If (ActiveSheet.UsedRange.Address = "$A$1") And (ActiveSheet.Range("$A$1").text = "") Then Exit Sub
        Wend
     
    Next i
    Stop
    'ActiveWorkbook.Save
    'objWorkbook.Close
    ActiveWorkbook.Close SaveChanges:=True
    End Sub
     

    Sunday, November 19, 2017 8:27 PM

All replies

  • FredericaS,
    re:  bad code

    1.  In this code line:   If columnHeadimg = "city" Then keepColumn = True,   Headimg is spelled wrong.
         "Option Explicit" as the first line in the module is good practice. (no quotes used)
        
    2.  You are running the code against the wrong workbook.
         "ThisWorkbook.Activate" activates the workbook containing the code.
          The last workbook opened is the active workbook, until code or the user changes it.

    3.  Don't assume the active sheet is the desired sheet.  Reference it using the sheet tab name...
         While currentColumn <=  _
          Workbooks("a_C_Track_20171101.xls").Worksheets("TextOnSheetTab").UsedRange.Columns.Count

    Always use  Option Explicit
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)


    • Edited by James Cone Friday, November 24, 2017 3:53 PM spelled
    Monday, November 20, 2017 2:14 AM
  • Hi,

    I'm wondering if the code is related with your question/description.
    Please explain what you want to do with your code, or what you mean by your description about [Shift], [F8], and [F5].

    # I'm trying to modify your code according to my guess (I'm not sure my guess is correct).

    Regards,

    Ashidacchi




    • Edited by Ashidacchi Monday, November 20, 2017 4:31 AM
    Monday, November 20, 2017 2:24 AM
  • Hi,

    I modified your code (as far as I can imagine your needs).

       (blank screen is shown while another workbook is opening for 5 seconds)  

    Code in [Delete columns] button:
    Private Sub btn_DeleteColumns_Click()
        ' -- open another workbook and close it after 5 seconds
        Dim filePath As String: filePath = "R:\00 北窓舎\01 PCサポート\00 MSDN_TechNet\00 Excel"
        Dim fileName As String: fileName = "Excel_Test.xlsx"
        Workbooks.Open (filePath & "\" & fileName)
        Application.Wait (Now + TimeValue("0:00:05"))
        Workbooks(fileName).Close
        ' ---
        Dim columnHeading As String
        ' --- read the data from the first column to end column
        ThisWorkbook.Activate
        Dim lastcol As Integer
        With ActiveSheet.UsedRange
            lastcol = .Columns(.Columns.Count).Column
        End With
        Dim myCol As Integer
        For myCol = lastcol To 1 Step -1
            columnHeading = Cells(1, myCol).Value
            ' --- Check whether to keep the column
            If (columnHeading = "reason") Or _
                (columnHeading = "first_name") Or _
                (columnHeading = "last_name") Or _
                (columnHeading = "employer_name") Or _
                (columnHeading = "city") Or _
                (columnHeading = "state") Or _
                (columnHeading = "date_of_birth") Or _
                (columnHeading = "ssn") Then
                ' --- do nothing
            Else
                ' --- delete column
                ActiveSheet.Columns(myCol).Delete
            End If
        Next
        ' ---
        MsgBox "Keep/Delete columns completed !!"
        'ActiveWorkbook.Close SaveChanges:=True
    End Sub
    
    Regards,

    Ashidacchi



    • Edited by Ashidacchi Monday, November 20, 2017 4:33 AM
    Monday, November 20, 2017 3:53 AM