1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro assistance

Discussion in 'Business Applications' started by Pedro15, Dec 22, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Pedro15

    Pedro15 Thread Starter

    Joined:
    Oct 5, 2008
    Messages:
    386
    Data imported to my computer comes in csv (USA) format.

    It contains Column A date and time in 5 minute increments Column B Values

    I need a macro to
    1. Replace date to leave just the time
    2. Delete rows to make increments 30 minutes in lieu of the 5
    3. Insert a chart

    With regard 1. the date will vary from day to day and accordingly I would like the macro to enable any date provided it is in the "dd-mm-yyyy" format that imported file receives.
    Had an attempt at rerecording 3 macros , which appears below, but the Replace macro did not work.

    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 22-12-2010 by Peter
    '
    
    '
        Cells.Replace What:="21-12-2010", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End Sub
    Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded 22-12-2010 by Peter
    '
    
    '
        Rows("3:7").Select
        Selection.Delete Shift:=xlUp
        Rows("4:8").Select
        Selection.Delete Shift:=xlUp
        Rows("5:9").Select
        Selection.Delete Shift:=xlUp
        Rows("6:10").Select
        Selection.Delete Shift:=xlUp
        Rows("7:11").Select
        Selection.Delete Shift:=xlUp
        Rows("8:12").Select
        Selection.Delete Shift:=xlUp
        Rows("9:13").Select
        Selection.Delete Shift:=xlUp
        Rows("10:14").Select
        Selection.Delete Shift:=xlUp
        Rows("11:15").Select
        Selection.Delete Shift:=xlUp
        Rows("12:16").Select
        Selection.Delete Shift:=xlUp
        Rows("13:17").Select
        Selection.Delete Shift:=xlUp
        Rows("14:18").Select
        Selection.Delete Shift:=xlUp
        Rows("15:19").Select
        Selection.Delete Shift:=xlUp
        Rows("16:20").Select
        Selection.Delete Shift:=xlUp
        Rows("17:21").Select
        Selection.Delete Shift:=xlUp
        Rows("18:22").Select
        Selection.Delete Shift:=xlUp
        Rows("19:23").Select
        Selection.Delete Shift:=xlUp
        Rows("20:24").Select
        Selection.Delete Shift:=xlUp
        Rows("21:25").Select
        Selection.Delete Shift:=xlUp
        Rows("22:26").Select
        Selection.Delete Shift:=xlUp
        Rows("23:27").Select
        Selection.Delete Shift:=xlUp
        Rows("24:28").Select
        Selection.Delete Shift:=xlUp
        Rows("25:29").Select
        Selection.Delete Shift:=xlUp
        Rows("26:30").Select
        Selection.Delete Shift:=xlUp
        Rows("27:30").Select
        Selection.Delete Shift:=xlUp
        Rows("28:31").Select
        Selection.Delete Shift:=xlUp
    End Sub
    Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 22-12-2010 by Peter
    '
    
    '
        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.SetSourceData Source:=Sheets("Chart energy to 530pm 211210"). _
            Range("A2:B28"), PlotBy:=xlColumns
        ActiveChart.Location Where:=xlLocationAsObject, Name:= _
            "Chart energy to 530pm 211210"
        With ActiveChart
            .HasTitle = False
            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).HasTitle = False
        End With
    End Sub
    
    Any assistance appreciated, as I am a real novice at macros.

    Pedro
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hi Pedro.

    Sheet 1 is how I imagine your raw data. Sheet2 is a copy of Sheet1 for "testing backup".

    With either sheet active, the code below inserts a new row 1, puts labels in A1 and B1, puts a time value of 30 minutes in D1, puts a formula in C2:Cn which returns "1" if column A value is (a multiple of) 30 minutes different from A2 or "#N/A" if column A value isn't (a multiple of) 30 minutes different from A2. It then deletes all the #N/A rows.

    That's how I would begin to go about it. Let me know what you think.

    Sub Macro1()
    Range("A1").EntireRow.Insert
    Range("A1") = "D-T"
    Range("B1") = "Value"
    Range("D1") = "0:30"
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("C2:C" & LastRow).FormulaR1C1 = _
    "=IF(OR(MINUTE(RC[-2])=MINUTE(R2C1)=TRUE,MINUTE(RC[-2])=MINUTE(R2C1+R1C4)=TRUE)=TRUE,1,#N/A)"
    Columns("C:C").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
    End Sub
     

    Attached Files:

  3. Pedro15

    Pedro15 Thread Starter

    Joined:
    Oct 5, 2008
    Messages:
    386
    Bomb,Thanks for your interest and your file which is very close to the one I receive from an external source.
    I have attached a copy .
    Bearing in mind I have a very limited knowledge of macros would you please add code that would insert a graph.
    Please note that Tab Name comes with the csv file and I have not changed it.

    Pedro
     
  4. Pedro15

    Pedro15 Thread Starter

    Joined:
    Oct 5, 2008
    Messages:
    386
    Am having trouble attaching my file.
    However this is what rows 1-6 look like
    Code:
             Date   Daily Energy[Wh]       20-12-10 6:25   488       20-12-10 6:30   549       20-12-10 6:35   602       20-12-10 6:40   662       20-12-10 6:45   728   
    The Tab name is"Chart energy20-12-10 and that came with the file already named. There is no other sheet in workbook.
    Properties of my file show it to be a "Microsoft Office Excel Comma Separated Values File"
    I am using Excel 2003.

    In looking at preview of row description you will need to imagine they run down and not across.
    By the way when I went to Manage Attachment I browsed and clicked on file , then went upload and nothing .

    Pedro
     
  5. Pedro15

    Pedro15 Thread Starter

    Joined:
    Oct 5, 2008
    Messages:
    386
    Must be early morning and brain not in working order.
    Saved as an excel document and hopefully it is attached.

    Pero
     

    Attached Files:

  6. Pedro15

    Pedro15 Thread Starter

    Joined:
    Oct 5, 2008
    Messages:
    386
    I tweaked code , using format Time rather than Replace, and using the excel file rather than the csv file it worked, but please read further comments below.
    The new code
    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 23-12-2010 by Peter
    '
    
    '
        Columns("A:A").Select
        Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
        Rows("3:9").Select
        Selection.Delete Shift:=xlUp
        Rows("4:6").Select
        Selection.Delete Shift:=xlUp
        Rows("5:11").Select
        Selection.Delete Shift:=xlUp
        Rows("6:8").Select
        Selection.Delete Shift:=xlUp
        Rows("7:12").Select
        Selection.Delete Shift:=xlUp
        Rows("8:11").Select
        Selection.Delete Shift:=xlUp
        Rows("9:13").Select
        Selection.Delete Shift:=xlUp
        Rows("10:14").Select
        Selection.Delete Shift:=xlUp
        Rows("11:15").Select
        Selection.Delete Shift:=xlUp
        Rows("12:16").Select
        Selection.Delete Shift:=xlUp
        Rows("13:17").Select
        Range("A17").Activate
        Selection.Delete Shift:=xlUp
        Rows("14:18").Select
        Selection.Delete Shift:=xlUp
        Rows("15:19").Select
        Selection.Delete Shift:=xlUp
        Rows("16:20").Select
        Selection.Delete Shift:=xlUp
        Rows("17:21").Select
        Selection.Delete Shift:=xlUp
        Rows("18:22").Select
        Selection.Delete Shift:=xlUp
        Rows("19:23").Select
        Selection.Delete Shift:=xlUp
        Rows("20:24").Select
        Selection.Delete Shift:=xlUp
        Rows("21:25").Select
        Selection.Delete Shift:=xlUp
        Rows("22:26").Select
        Selection.Delete Shift:=xlUp
        Rows("23:26").Select
        Selection.Delete Shift:=xlUp
        Rows("24:28").Select
        Selection.Delete Shift:=xlUp
        Rows("27:40").Select
        Selection.ClearContents
        Range("J5").Select
        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.SetSourceData Source:=Sheets("Chart energy 201210").Range( _
            "A2:B26"), PlotBy:=xlColumns
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart energy 201210"
        With ActiveChart
            .HasTitle = False
            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).HasTitle = False
        End With
    End Sub
    
    It worked perfectly on file I attached earlier and I thought the secret was the excel file rather than the csv format.
    However, when I tried it on another file ,saved in excel, I got a "Runtime error 9,subscript out of range" with offending piece
    " ActiveChart.SetSourceData Source:=Sheets("Chart energy 201210").Range( _
    "A2:B26"), PlotBy:=xlColumns"
    The errant workbook also has another Tab named "Chart 1", whereas the file I attached (which works) only has a Tab named "Chart energy 201210".

    Any suggestions why and assuming it is something simple can code be shortened?

    Thanks
    Pedro
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "Must be early morning" -- it's always early morning somewhere. :D

    Any chart issues are secondary, IMO. I can't see how the preceding code -- "select -- delete -- repeat" -- can work. Take this:

    Rows("3:9").Select
    Selection.Delete Shift:=xlUp
    Rows("4:6").Select
    Selection.Delete Shift:=xlUp


    Because: once you've deleted 3:9, 4:6 aren't where they used to be. That's exactly why I took the "formulas in a 'helper' column that return errors for superfluous rows for subsequent bulk deletion using 'SpecialCells(xlCellTypeFormulas, 16)'" route. Do you understand?

    In your attachment, the sheet has a header row. A2 is 06:25. You then (unless I'm mistaken) want A8 (06:55), A14 (06:55), and so on. Row 2, then every 6th row after row 2.

    Use this in column C:

    =MOD(ROW()-1,6)

    (the -1 is to account for the header row).

    That gives you 1 for the required rows. Then expand it to:

    =MOD(ROW()-1,6)=1

    and expand it again to:

    =IF(MOD(ROW()-1,6)=1=TRUE,1,#N/A)

    That will tag all your "not required" rows as #N/A, which sets up the Columns("C:C").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete -- see? :)
     
  8. Pedro15

    Pedro15 Thread Starter

    Joined:
    Oct 5, 2008
    Messages:
    386
    Thanks Bomb for your effort and explanation.

    At first pass my eyes glazed over so will need a bit of time to digest.

    Good news is I got my attempt at coding to work.
    Date is removed to enable meaningful graph, and intervals are set at 30 minutes.

    File with macro is attached.

    Once again thanks for your assistance.

    Pedro
     

    Attached Files:

  9. Pedro15

    Pedro15 Thread Starter

    Joined:
    Oct 5, 2008
    Messages:
    386
    Best laid plans .Macro did not show up on file

    It is
    Code:
      Sub Macro1()
      '
      ' Macro1 Macro
      ' Macro recorded 23-12-2010 by Peter
      '
       
      '
          Columns("A:A").Select
          Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
          Rows("3:7").Select
          Selection.Delete Shift:=xlUp
          Rows("4:8").Select
          Selection.Delete Shift:=xlUp
          Rows("5:9").Select
          Selection.Delete Shift:=xlUp
          Rows("6:10").Select
          Selection.Delete Shift:=xlUp
          Rows("7:11").Select
          Selection.Delete Shift:=xlUp
          Rows("8:12").Select
          Selection.Delete Shift:=xlUp
          Rows("9:13").Select
          Selection.Delete Shift:=xlUp
          Rows("10:14").Select
          Selection.Delete Shift:=xlUp
          Rows("11:15").Select
          Selection.Delete Shift:=xlUp
          Rows("12:16").Select
          Selection.Delete Shift:=xlUp
          Rows("13:17").Select
          Selection.Delete Shift:=xlUp
          Rows("14:18").Select
          Selection.Delete Shift:=xlUp
          Rows("15:19").Select
          Selection.Delete Shift:=xlUp
          Rows("16:20").Select
          Selection.Delete Shift:=xlUp
          Rows("17:21").Select
          Selection.Delete Shift:=xlUp
          Rows("18:22").Select
          Selection.Delete Shift:=xlUp
          Rows("19:23").Select
          Selection.Delete Shift:=xlUp
          Rows("20:24").Select
          Selection.Delete Shift:=xlUp
          Rows("21:25").Select
          Selection.Delete Shift:=xlUp
          Rows("22:26").Select
          Selection.Delete Shift:=xlUp
          Rows("23:27").Select
          Selection.Delete Shift:=xlUp
          Rows("24:28").Select
          Selection.Delete Shift:=xlUp
          Rows("25:29").Select
          Selection.Delete Shift:=xlUp
          Rows("26:30").Select
          Selection.Delete Shift:=xlUp
          Rows("27:31").Select
          Selection.Delete Shift:=xlUp
          Rows("28:32").Select
          Selection.Delete Shift:=xlUp
          Rows("29:33").Select
          Selection.Delete Shift:=xlUp
          Rows("30:34").Select
          Selection.Delete Shift:=xlUp
          Rows("31:35").Select
          Selection.Delete Shift:=xlUp
          Rows("32:36").Select
          Selection.Delete Shift:=xlUp
          Rows("31:80").Select
          Selection.Delete Shift:=xlUp
          ActiveWindow.ScrollRow = 10
          ActiveWindow.ScrollRow = 9
          ActiveWindow.ScrollRow = 8
          ActiveWindow.ScrollRow = 7
          ActiveWindow.ScrollRow = 6
          ActiveWindow.ScrollRow = 4
          ActiveWindow.ScrollRow = 3
          ActiveWindow.ScrollRow = 2
          ActiveWindow.ScrollRow = 1
          Range("K30").Select
      End Sub
    Pedro
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,591
    First Name:
    Hans
    I think you saved your macro to the personal.xls instead of the current Excel file, that's why it doesn't show in the file.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/969941

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice