Macro assistance

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
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
 

Attachments

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
 

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
 

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
 
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? :)
 

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
 

Attachments

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top