Well, that bit works OK for me. The questions I would ask are what sheet
is active at the time? What is the value of k immediately before the
rows... select is run? What is the macro selecting?
There are a number of issues with your code. One which concerns me in
particular is the line
Dim i, j, k, l As Double
This will dim i, j, and k as variants and l as a double. In fact I think
you want them all as Longs, not doubles. so use
Dim i as Long, j as Long, k as Long, l as Long
There are other similar Dim statements where the As will only apply to the
last variable declared in the line.
Generally when you select a range and then perform an action it is more
efficient, simpler, easier to follow, and less error-prone to just perform
the action on the range. For example, you have
Rows("102:" & k).Select
Selection.Delete Shift:=xlUp
This could be
Rows("102:" & k).Delete Shift:=xlUp
Regards
David Grugeon
On 30 April 2013 13:56, excel learner <knowledgeforex...@gmail.com> wrote:
> this particular line it is Just skipping Rows("102:" & k).Select
>
> and deletion line also not performed.
>
>
>
>
> On Mon, Apr 29, 2013 at 4:19 AM, David Grugeon <da...@grugeon.com.au>wrote:
>
>> Thanks, Ram
>>
>> So you are saying that it correctly does the line
>>
>> Rows("102:" & k).Select
>>
>> At that point you can look at the spreadsheet and see that the relevant
>> rows have been selected. Then, when you run the next line
>> Selection.Delete Shift:=xlUp
>>
>> It does not delete the selection.
>>
>> What does it do? Are the rows still selected? If not, where is the
>> active cell after this line is run?
>>
>> Regards
>> David Grugeon
>>
>>
>>
>> On 28 April 2013 16:36, excel learner <knowledgeforex...@gmail.com>wrote:
>>
>>> hi David,
>>>
>>> I tried step by step execution but still it is executing fine without
>>> any error but deletion part in first transaction tab is not done..
>>>
>>> Thanks & Regards,
>>> Ram
>>>
>>>
>>> On Sun, Apr 28, 2013 at 12:04 PM, David Grugeon <da...@grugeon.com.au>wrote:
>>>
>>>> You have highlighted 5 lines
>>>>
>>>> Try stepping through the code and finding out which line does not work
>>>> as required.
>>>>
>>>>
>>>>
>>>> Regards
>>>> David Grugeon
>>>>
>>>>
>>>>
>>>> On 28 April 2013 16:25, <knowledgeforex...@gmail.com> wrote:
>>>>
>>>>> Hi Experts,
>>>>>
>>>>> i have come across an issue where in auto filter code is skipping
>>>>> over without performing the required action and running next course of
>>>>> action.
>>>>>
>>>>> I Have highlighted the line where am getting error can any one correct
>>>>> me where am facing this issue.
>>>>>
>>>>> Macro is executing well but this particular lines it is not performing
>>>>> the action.
>>>>>
>>>>>
>>>>> ActiveSheet.Range("A101:AY" & k).AutoFilter Field:=51,
>>>>> Criteria1:="<>" & FileName
>>>>> Rows("102:" & k).Select
>>>>> Selection.Delete Shift:=xlUp
>>>>> Range("A101").Select
>>>>> Selection.AutoFilter
>>>>>
>>>>>
>>>>> *complete code*
>>>>>
>>>>> Public Sub OPEX_Reporting()
>>>>>
>>>>> Dim Master As Workbook ' Macro and Master Workbook
>>>>> 'Dim Linked As Workbook ' CCA P&L Linked File
>>>>> Dim NewFile As Workbook ' New File
>>>>>
>>>>> Dim macro, CCA As Worksheet ' Worksheets in Macro and Master Workbook
>>>>> 'Dim Report, CON As Worksheet ' Worksheets in CCA P&L Linked File
>>>>> Dim sh As Worksheet ' Searching for sheets
>>>>>
>>>>> Dim rng As Range
>>>>>
>>>>> Dim i, j, k, l As Double
>>>>> Dim Outerloop, Innerloop As Integer
>>>>>
>>>>> Dim PATH, PATH1 As String
>>>>> Dim FileName As Double
>>>>> Dim FileNamesave As Double
>>>>>
>>>>> Set Master = ThisWorkbook
>>>>>
>>>>> Set macro = Master.Sheets("Macro")
>>>>>
>>>>>
>>>>> i = macro.Cells(Rows.Count, "A").End(xlUp).Row
>>>>>
>>>>> Application.ScreenUpdating = False
>>>>> Application.DisplayAlerts = False
>>>>>
>>>>> PATH = macro.Range("C4")
>>>>>
>>>>> Dim Answer As String
>>>>> Dim MyNote As String
>>>>>
>>>>> 'Place your text here
>>>>> MyNote = "You are about to run the Macro. Are you Sure?" & Chr(13)
>>>>> & Chr(13) & "If Yes! Make sure the below Path is empty - " & Chr(13) &
>>>>> Chr(13) & PATH
>>>>>
>>>>> 'Display MessageBox
>>>>> Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Macro Confirmation
>>>>> Message !!!")
>>>>>
>>>>> If Answer = vbNo Then
>>>>> 'Code for No button Press
>>>>> 'MsgBox "You pressed NO!"
>>>>> End
>>>>> Else
>>>>> 'Code for Yes button Press
>>>>> 'MsgBox "You pressed Yes!"
>>>>> End If
>>>>>
>>>>> For Outerloop = 8 To 10
>>>>> j = macro.Cells(Outerloop, Columns.Count).End(xlToLeft).Column
>>>>>
>>>>>
>>>>> FileName = macro.Cells(Outerloop, 1)
>>>>>
>>>>>
>>>>> Set NewFile = Workbooks.Add
>>>>>
>>>>> For Innerloop = 3 To j
>>>>> Dim CC As String
>>>>>
>>>>> CC = ""
>>>>> CC = macro.Cells(Outerloop, Innerloop)
>>>>> Set sh = Master.Worksheets(CC)
>>>>> sh.Activate
>>>>> ActiveSheet.Copy before:=NewFile.Sheets(1)
>>>>> k = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
>>>>>
>>>>> NewFile.Sheets(CC).Select
>>>>> Cells.Find(What:="Mapping Data", After:=ActiveCell,
>>>>> LookIn:=xlFormulas, _
>>>>> LookAt:=xlPart, SearchOrder:=xlByRows,
>>>>> SearchDirection:=xlPrevious, _
>>>>> MatchCase:=False, SearchFormat:=False).Activate
>>>>>
>>>>> ' If Sheets(CC).FilterMode = True Then
>>>>> ' Selection.AutoFilter
>>>>> ' Else
>>>>> ' Selection.AutoFilter
>>>>> ' End If
>>>>>
>>>>>
>>>>> If CC = "Transaction Details" Then
>>>>> ActiveSheet.Range("A101:AY" & k).AutoFilter Field:=51,
>>>>> Criteria1:="<>" & FileName
>>>>> Rows("102:" & k).Select
>>>>> Selection.Delete Shift:=xlUp
>>>>> Range("A101").Select
>>>>> Selection.AutoFilter
>>>>> ElseIf CC = "Phased Actuals" Then
>>>>> ActiveSheet.Range("A49:AY" & k).AutoFilter Field:=51,
>>>>> Criteria1:="<>" & FileName
>>>>> Rows("50:" & k).Select
>>>>> Selection.Delete Shift:=xlUp
>>>>> Range("A49").Select
>>>>> Selection.AutoFilter
>>>>> ElseIf CC = "Summary" Then
>>>>> Rows("11:11").Select
>>>>> ActiveSheet.Range("$A$11:$BF$19" & k).AutoFilter Field:=50,
>>>>> Criteria1:="<>" & FileName
>>>>> Rows("12:" & k).Select
>>>>> Selection.Delete Shift:=xlUp
>>>>> Range("a11").Select
>>>>> Selection.AutoFilter
>>>>> 'ElseIf CC = "Nat Exp Vs Plan" Then
>>>>> 'ActiveSheet.Range("A94:AH" & k).AutoFilter Field:=35,
>>>>> Criteria1:="<>" & FileName
>>>>> 'Rows("95:" & k).Select
>>>>> 'Selection.Delete Shift:=xlUp
>>>>> 'Range("A94").Select
>>>>> 'Selection.AutoFilter
>>>>> 'Else
>>>>> 'ActiveSheet.Range("A94:AH" & k).AutoFilter Field:=34,
>>>>> Criteria1:="<>" & FileName
>>>>> 'Rows("95:" & k).Select
>>>>> 'Selection.Delete Shift:=xlUp
>>>>> 'Range("A94").Select
>>>>> 'Selection.AutoFilter
>>>>> End If
>>>>>
>>>>> Next
>>>>> NewFile.Activate
>>>>> On Error Resume Next
>>>>> NewFile.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
>>>>> NewFile.SaveAs FileName:=PATH & "\" & FileName,
>>>>> FileFormat:=xlOpenXMLWorkbook
>>>>> NewFile.Close
>>>>> Next
>>>>> macro.Activate
>>>>> MsgBox "You have run the Macro Successfully!!!"
>>>>>
>>>>> Application.DisplayAlerts = True
>>>>> Application.ScreenUpdating = True
>>>>> End Sub
>>>>>
>>>>>
>>>>> Thanks & Regards,
>>>>> Ram
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>>>> https://www.facebook.com/discussexcel
>>>>>
>>>>> FORUM RULES
>>>>>
>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>> Advice will not get quick attention or may not be answered.
>>>>> 2) Don't post a question in the thread of another member.
>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>> measure.
>>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>> 5) Jobs posting is not allowed.
>>>>> 6) Sharing copyrighted material and their links is not allowed.
>>>>>
>>>>> NOTE : Don't ever post confidential data in a workbook. Forum owners
>>>>> and members are not responsible for any loss.
>>>>> ---
>>>>> You received this message because you are subscribed to the Google
>>>>> Groups "MS EXCEL AND VBA MACROS" group.
>>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>>> an email to excel-macros+unsubscr...@googlegroups.com.
>>>>>
>>>>> To post to this group, send email to excel-macros@googlegroups.com.
>>>>> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>>>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>>>
>>>>>
>>>>>
>>>>
>>>> --
>>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>>> https://www.facebook.com/discussexcel
>>>>
>>>> FORUM RULES
>>>>
>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.
>>>> 2) Don't post a question in the thread of another member.
>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.
>>>> 4) Acknowledge the responses you receive, good or bad.
>>>> 5) Jobs posting is not allowed.
>>>> 6) Sharing copyrighted material and their links is not allowed.
>>>>
>>>> NOTE : Don't ever post confidential data in a workbook. Forum owners
>>>> and members are not responsible for any loss.
>>>> ---
>>>> You received this message because you are subscribed to a topic in the
>>>> Google Groups "MS EXCEL AND VBA MACROS" group.
>>>> To unsubscribe from this topic, visit
>>>> https://groups.google.com/d/topic/excel-macros/ZNZLEMs7iP0/unsubscribe?hl=en
>>>> .
>>>> To unsubscribe from this group and all its topics, send an email to
>>>> excel-macros+unsubscr...@googlegroups.com.
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com.
>>>> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>>
>>>>
>>>>
>>>
>>> --
>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>> https://www.facebook.com/discussexcel
>>>
>>> FORUM RULES
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>> 2) Don't post a question in the thread of another member.
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>> 4) Acknowledge the responses you receive, good or bad.
>>> 5) Jobs posting is not allowed.
>>> 6) Sharing copyrighted material and their links is not allowed.
>>>
>>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>> members are not responsible for any loss.
>>> ---
>>> You received this message because you are subscribed to the Google
>>> Groups "MS EXCEL AND VBA MACROS" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to excel-macros+unsubscr...@googlegroups.com.
>>> To post to this group, send email to excel-macros@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>
>>>
>>>
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to a topic in the
>> Google Groups "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/excel-macros/ZNZLEMs7iP0/unsubscribe?hl=en
>> .
>> To unsubscribe from this group and all its topics, send an email to
>> excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
