• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to dynamically select the cells

narsing rao

Member
Hi,

i have created a macro for report generation , in this process i will copy data from one sheet to another . once i copy, rows below the copied part has to be deleted as they are not required, in this macro Rows("28:29").Select are showing as deleted because i have copied the data till 27th row remaining two rows(28 and 29 ) from previous days report those i don't want in this today's report so i have deleted them.but i need this to be selected dynamically because count of rows may change daily.

this is created purely using macros please bare with code.
Code:
Sub Macroubpsreport()
'
' Macroubpsreport Macro
'

'
    Cells.Select
    Application.CutCopyMode = False
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("L:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("N:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("O:Q").Select
    Selection.Delete Shift:=xlToLeft
    Columns("P:AG").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AU$1822").AutoFilter Field:=9, Criteria1:=Array( _
        "With Assignee", "With CCB Approver After Patch Initiation", _
        "With Consultation Team for Ownership", _
        "With Release Manager After RCD Initiation", _
        "With Release Manager Team For RCD Approval", "With Requestor For Clarification" _
        , "With Reviewer For Clarification", "With Support Team for Ownership"), _
        Operator:=xlFilterValues
    ActiveSheet.Range("$A$1:$AU$1822").AutoFilter Field:=2, Criteria1:=Array( _
        "ANDHRABANK", "DENA", "EDBE", "EXIML3SAS_FINATS", "ICICI", "KBL", "NIBL", _
        "NKGSBINDIA_FINIMPL", "PNB", "SOHAR_IMPL", "VB"), Operator:=xlFilterValues
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    Range("O2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("O2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="d", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("SLA Status").Select
    Range("B2").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 13
    Rows("28:29").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("E19").Select
    ActiveWorkbook.RefreshAll
End Sub
 
Hi,

The best way to do it in my opinion is to delete all rows already in report first and then copy the new data... this way you don't need to figure out how many rows of the report remain from the previous day.

One other thing you may consider is using a Pivot Table instead!

On a side note, if you upload the sample file we can help clean up the code... in fact, since it was recorded, there is a lot of unnecessary actions that are just slowing it down (all ActiveWindow.ScrollRow and ActiveWindow.ScrollColumn, for instance, can be deleted)

Hope this helps
 
Hi Asheesh & Pcoosta,

thanks for your reply and i am a big fan of Pcosta as i am fallowing his other post and how he is solving the problems he also helped me previously, Pcosta what you have suggested is quite simple solution i never thought of it, as i gone through the report closely there is formula applied in column Q the sheet where i am copying the data.if deleting what changes i have to do in macro which delete the entire row and columns upto P column which occupied previous day data and it should be dynamic.

formula:

=IF(P2<=0,"SLA NOT MET",IF(AND(P2>0,P2<=2),"Between 0 To 2 Days",IF(AND(P2>2,P2<=7),"Between 3 To 7 Days",IF(AND(P2>7,P2<30),"Between 8 To 30",IF(P2>30,"Above 30 Days")))))


i cant copy sample excel sheet as it is against my company policy to share date online.

please help

the sheet looks like below .

upload_2017-2-23_14-7-30.png
 
below given screen shots may give some idea.sheet1 is the new data where i do all the calculations and copy the data into SLA status sheet
upload_2017-2-23_14-35-30.png


in this i have formula in Q column once i copy the data the formula calculate
day and populate info in the same column.




upload_2017-2-23_14-35-50.png

in summery sheet i will get results like below.
upload_2017-2-23_14-35-8.png
 
Hi !
i cant copy sample excel sheet as it is against my company policy to share date online.
But you can easily join a sample workbook respecting original layout !

As advised before to log in :​

chandoo-sample-file-jpg.34170
 
Hi Asheesh & Pcoosta,

thanks for your reply and i am a big fan of Pcosta as i am fallowing his other post and how he is solving the problems he also helped me previously, Pcosta what you have suggested is quite simple solution i never thought of it, as i gone through the report closely there is formula applied in column Q the sheet where i am copying the data.if deleting what changes i have to do in macro which delete the entire row and columns upto P column which occupied previous day data and it should be dynamic.

formula:

=IF(P2<=0,"SLA NOT MET",IF(AND(P2>0,P2<=2),"Between 0 To 2 Days",IF(AND(P2>2,P2<=7),"Between 3 To 7 Days",IF(AND(P2>7,P2<30),"Between 8 To 30",IF(P2>30,"Above 30 Days")))))


i cant copy sample excel sheet as it is against my company policy to share date online.

please help

the sheet looks like below .

View attachment 38867
That is not a problem... the subroutine can add the formula back to column Q after pasting the new data.
I will see what I can do from the screenshots and the code provided.
 
Untested, but see if the following works:
Code:
Sub Macroubpsreport()
 
    With Cells
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
   
    Columns("A:A").Delete Shift:=xlToLeft
    Columns("H:H").Delete Shift:=xlToLeft
    Columns("I:J").Delete Shift:=xlToLeft
    Columns("K:N").Delete Shift:=xlToLeft
    Columns("L:M").Delete Shift:=xlToLeft
    Columns("N:N").Delete Shift:=xlToLeft
    Columns("O:Q").Delete Shift:=xlToLeft
    Columns("P:AG").Delete Shift:=xlToLeft
   
    Sheets("Sheet1").Range("A:AU").AutoFilter Field:=9, Criteria1:=Array( _
        "With Assignee", "With CCB Approver After Patch Initiation", _
        "With Consultation Team for Ownership", _
        "With Release Manager After RCD Initiation", _
        "With Release Manager Team For RCD Approval", "With Requestor For Clarification" _
        , "With Reviewer For Clarification", "With Support Team for Ownership"), _
        Operator:=xlFilterValues

    Sheets("Sheet1").Range("A:AU").AutoFilter Field:=2, Criteria1:=Array( _
        "ANDHRABANK", "DENA", "EDBE", "EXIML3SAS_FINATS", "ICICI", "KBL", "NIBL", _
        "NKGSBINDIA_FINIMPL", "PNB", "SOHAR_IMPL", "VB"), Operator:=xlFilterValues
   
    Sheets("Sheet1").Range("O2", Range("O2").End(xlDown)).TextToColumns Destination:=Sheets("Sheet1").Range("O2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="d", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
   
    Sheets("SLA Status").Rows("2:" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
    Sheets("Sheet1").Range(Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown)), Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown)).End(xlToRight)).Copy Sheets("SLA Status").Range("B2")
    Sheets("SLA Status").Range("Q2").Formula = "=IF(P2<=0,""SLA NOT MET"",IF(AND(P2>0,P2<=2),""Between 0 To 2 Days"",IF(AND(P2>2,P2<=7),""Between 3 To 7 Days"",IF(AND(P2>7,P2<30),""Between 8 To 30"",IF(P2>30,""Above 30 Days"")))))"
    Sheets("SLA Status").Range("Q2:Q" & Cells(Rows.Count, "B").End(xlUp).Row).FillDown
   
    ActiveWorkbook.RefreshAll

End Sub

Hope it helps
 
Hi Pcosta,

as i expected the code is working like charm, but its not displaying serial no as shown below.


upload_2017-2-23_17-13-34.png

and after 27th column its unnecessarily displaying SLA NOT MET in q row till end

upload_2017-2-23_17-12-53.png

please check this
 
when you give solution please mention where i went wrong. am very much entrusted in knowing my mistake and in future i will correct those ur code is very neat i am easy able to under stand now.
and also can you please check my other post just i have posted.

Thanks in advance...
 
its not displaying serial no

That may be because in your code you were pasting the data after "B2" so I kept that... but it seems that what you need is to paste in "A2" so replace line after ".clearcontents" with:
Code:
Sheets("Sheet1").Range(Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown)), Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown)).End(xlToRight)).Copy Sheets("SLA Status").Range("A2")

after 27th column its unnecessarily displaying SLA NOT MET in q row till end
That was my mistake... I didn't correctly reference the sheet where it can find the last used row. To fix, replace the line above ".RefreshAll" with:
Code:
Sheets("SLA Status").Range("Q2:Q" & Sheets("SLA Status").Cells(Rows.Count, "A").End(xlUp).Row).FillDown

also can you please check my other post

I will do that!
 
Hi Pcosta,

i have made changes as you mentioned ..but i am getting call ids instead of serial no ..see below

upload_2017-2-23_18-27-23.png

and still i can see the SLA NOT MET after 27th column in q row

upload_2017-2-23_18-28-51.png

changes are :
Code:
 Sheets("SLA Status").Rows("2:" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
    Sheets("Sheet1").Range(Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown)), Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown)).End(xlToRight)).Copy Sheets("SLA Status").Range("A2")
    Sheets("SLA Status").Range("Q2").Formula = "=IF(P2<=0,""SLA NOT MET"",IF(AND(P2>0,P2<=2),""Between 0 To 2 Days"",IF(AND(P2>2,P2<=7),""Between 3 To 7 Days"",IF(AND(P2>7,P2<30),""Between 8 To 30"",IF(P2>30,""Above 30 Days"")))))"
    Sheets("SLA Status").Range("Q2:Q" & Cells(Rows.Count, "B").End(xlUp).Row).FillDown

and
Code:
 ActiveWorkbook.Sheets("SLA Status").Range("Q2:Q" & Sheets("SLA Status").Cells(Rows.Count, "A").End(xlUp).Row).FillDown

please check
 
Pcosta,

i have wrongly pasted the code which you have given ..now only the problem is serial numbers are not displaying instead it's showing call id's
 
I see...
So the code was correct the first time around... it should paste in "B2" so let's change the first back to:
Code:
Sheets("Sheet1").Range(Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown)), Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown)).End(xlToRight)).Copy Sheets("SLA Status").Range("B2")

Now, about it not showing the Sr. No... is the serial number copied over from "Sheet1" also or is it simply 1, 2, 3, 4... and so on?

If it is a simple sequence, we can add it in the code... if it should be copied over, I need to know exactly where the Sr. No. are so I can code for it to.
 
Last edited:
its simply sr.no ...not copied from anywhere...and it should be upto columns according the report generated i mean it should dynamically check columns of the data.....

Thanks for the other reply i will check that ...
 
Easy enough...
Replace all with
Code:
Sub Macroubpsreport()
    With Cells
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    Columns("A:A").Delete Shift:=xlToLeft
    Columns("H:H").Delete Shift:=xlToLeft
    Columns("I:J").Delete Shift:=xlToLeft
    Columns("K:N").Delete Shift:=xlToLeft
    Columns("L:M").Delete Shift:=xlToLeft
    Columns("N:N").Delete Shift:=xlToLeft
    Columns("O:Q").Delete Shift:=xlToLeft
    Columns("P:AG").Delete Shift:=xlToLeft
 
    Sheets("Sheet1").Range("A:AU").AutoFilter Field:=9, Criteria1:=Array( _
        "With Assignee", "With CCB Approver After Patch Initiation", _
        "With Consultation Team for Ownership", _
        "With Release Manager After RCD Initiation", _
        "With Release Manager Team For RCD Approval", "With Requestor For Clarification" _
        , "With Reviewer For Clarification", "With Support Team for Ownership"), _
        Operator:=xlFilterValues

    Sheets("Sheet1").Range("A:AU").AutoFilter Field:=2, Criteria1:=Array( _
        "ANDHRABANK", "DENA", "EDBE", "EXIML3SAS_FINATS", "ICICI", "KBL", "NIBL", _
        "NKGSBINDIA_FINIMPL", "PNB", "SOHAR_IMPL", "VB"), Operator:=xlFilterValues
 
    Sheets("Sheet1").Range("O2", Range("O2").End(xlDown)).TextToColumns Destination:=Sheets("Sheet1").Range("O2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="d", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
 
    Sheets("SLA Status").Rows("2:" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
    Sheets("Sheet1").Range(Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown)), Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A2").End(xlDown)).End(xlToRight)).Copy Sheets("SLA Status").Range("B2")
    Sheets("SLA Status").Range("Q2").Formula = "=IF(P2<=0,""SLA NOT MET"",IF(AND(P2>0,P2<=2),""Between 0 To 2 Days"",IF(AND(P2>2,P2<=7),""Between 3 To 7 Days"",IF(AND(P2>7,P2<30),""Between 8 To 30"",IF(P2>30,""Above 30 Days"")))))"
    Sheets("SLA Status").Range("A2").Value = 1
    Sheets("SLA Status").Range("Q2:Q" & Sheets("SLA Status").Cells(Rows.Count, "B").End(xlUp).Row).FillDown
    Sheets("SLA Status").Range("A2").AutoFill Destination:=Range("A2:A" & Sheets("SLA Status").Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillSeries
 
    ActiveWorkbook.RefreshAll

End Sub
 
Hi Pcosta,

i am getting error auto fill method is failed

upload_2017-2-24_9-58-29.png

its not calculating the sr. no only 1 is displayed at A2 after that its blank
 
Hi, sorry about that... it is a simple missing sheet reference :(

Code:
Sheets("SLA Status").Range("A2").AutoFill Destination:=Sheets("SLA Status").Range("A2:A" & Sheets("SLA Status").Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillSeries
 
Back
Top