Results 1 to 10 of 10

Thread: [RESOLVED] (Different Macro) if sheet is blank then do nothing(macro modification)

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2020
    Posts
    24

    Resolved [RESOLVED] (Different Macro) if sheet is blank then do nothing(macro modification)

    Code:
    Sub STEP6CORRECTIONPENDING()
    Dim Wb1 As Workbook, Wb2 As Workbook
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 5000: Lr2 = 5000
    Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
    Set Ws1 = Wb1.Worksheets(1)
    Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\Error.xlsx")
    Set Ws2 = Wb2.Worksheets(1)
    
    Dim rngSrch As Range: Set rngSrch = Ws2.Range("C1:C" & Lr2 & "")
    Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")
    
    Dim Cnt As Long
        For Cnt = Lr2 To 1 Step -1
        Dim MtchedCel As Variant
         Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
            If Not MtchedCel Is Nothing Then
             rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
            Else
            End If
            
        Next Cnt
     Wb1.Close SaveChanges:=True
     Wb2.Close SaveChanges:=True
    End Sub

    This macro works perfect
    but if the sheet is blank Or sheet doesn't have data then it creates error & i dont want that to happen
    If sheet is blank then dont do anything
    plz help me in solving the same

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: (Different Macro) if sheet is blank then do nothing(macro modification)

    Assuming that a valid sheet has a non-blank cell A1, the following would work:

    Code:
    Sub STEP6CORRECTIONPENDING()
    Dim Wb1 As Workbook, Wb2 As Workbook
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 5000: Lr2 = 5000
    Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
    Set Ws1 = Wb1.Worksheets(1)
    Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\Error.xlsx")
    Set Ws2 = Wb2.Worksheets(1)
    
    Dim rngSrch As Range: Set rngSrch = Ws2.Range("C1:C" & Lr2 & "")
    Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")
    
    Dim Cnt As Long
        If ActiveSheet.Cells(1, 1) = "" Then Exit Sub ' Test Cell A1 for data, exit if blank
        For Cnt = Lr2 To 1 Step -1
        Dim MtchedCel As Variant
         Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
            If Not MtchedCel Is Nothing Then
             rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
            Else
            End If
            
        Next Cnt
     Wb1.Close SaveChanges:=True
     Wb2.Close SaveChanges:=True
    End Sub

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2020
    Posts
    24

    Re: (Different Macro) if sheet is blank then do nothing(macro modification)

    Jdc2000 Thnx Alot Sir for ur Great Help
    But Do not consider cell A to check wheather it has blank sheet or not (there may be data in the sheet, So if complete sheet is blank then only it shoud exit )
    & the modified macro is runing Perfect Sir
    Last edited by mail2tradesmart; Jul 24th, 2020 at 11:42 AM.

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: (Different Macro) if sheet is blank then do nothing(macro modification)


  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jul 2020
    Posts
    24

    Re: (Different Macro) if sheet is blank then do nothing(macro modification)

    jdc2000 Sir i tried to modify the code but i am unable to make it perfect sir
    Plz have a look Sir

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: (Different Macro) if sheet is blank then do nothing(macro modification)

    What modifications did you make, and what were you wanting them to do? We need details of your idea for a check for a blank sheet. Also, post the code you tried.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jul 2020
    Posts
    24

    Re: (Different Macro) if sheet is blank then do nothing(macro modification)

    I used a line
    Code:
    If ActiveSheet.IsBlank Then Exit Sub
    Got error
    Code:
    If WorksheetFunction.CountA(ActiveSheet.UsedRange) = 0 Then Exit Sub
    Got error

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Jul 2020
    Posts
    24

    Re: (Different Macro) if sheet is blank then do nothing(macro modification)

    Skip this problem
    I have another idea
    I will make the data be present in the sheet
    Problem Solved
    Thnx Alot Jdc2000 Sir for helping me in solving this problem
    Have a Awesome Day

  9. #9
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: [RESOLVED] (Different Macro) if sheet is blank then do nothing(macro modification

    Not sure where you got those code ideas. Did you even look at the links I posted? Since we do not know what sort of data format your sheets are supposed to contain, we can only guess as to the best way to determine if a sheet in your workbook is "empty" or has an incorrect data format. The easy way is to check the data in a cell that should ALWAYS have data, and if it is empty then exit the Sub. If you do not have such a cell, then you have to try other methods. Alternatively, you can trap the error you are getting in your existing code on a blank sheet and exit the Sub if that occurs.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jul 2020
    Posts
    24

    Re: [RESOLVED] (Different Macro) if sheet is blank then do nothing(macro modification

    No Problem Jdc2000 Sir this problem I will manage it & it's manageble
    I saw the link given by you
    Plz have a look to other post
    Leave this problem for me, i will take care of this

Posting Permissions

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



Click Here to Expand Forum to Full Width