-
Jul 24th, 2020, 08:54 AM
#1
Thread Starter
Junior Member
[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
-
Jul 24th, 2020, 10:25 AM
#2
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
-
Jul 24th, 2020, 11:33 AM
#3
Thread Starter
Junior Member
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.
-
Jul 24th, 2020, 12:11 PM
#4
Re: (Different Macro) if sheet is blank then do nothing(macro modification)
-
Jul 24th, 2020, 01:26 PM
#5
Thread Starter
Junior Member
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
-
Jul 24th, 2020, 01:48 PM
#6
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.
-
Jul 24th, 2020, 01:53 PM
#7
Thread Starter
Junior Member
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
-
Jul 24th, 2020, 02:11 PM
#8
Thread Starter
Junior Member
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
-
Jul 24th, 2020, 02:14 PM
#9
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.
-
Jul 24th, 2020, 02:30 PM
#10
Thread Starter
Junior Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|