Can this macro be shortened

[pedro-egoli]

Have a macro which I recorded step by step to delete date(from date and time) in one column and data in another so that the only info is at half hourly intervals.

Data is saved in excel and covers info at 5 minute intervals like this in columns A & B

24-07-11 6:56	14
24-07-11 7:01	26

The macro I have at the moment when run delivers the following

7:01:59 AM	26
7:31:59 AM	404

The macro itsel is

 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

Is there a shorter macro which will do the same.

Thanks
Pedro

 

[Smitty]

If the code works for you, then the first thing I'd do is get rid of the select statements.

Generally, when you see Select followed by Selection, you can eliminate both statements and pull the remaining code together.

After that if you can identify some consistency in the deleted range you could use a For...Next construct to loop through the rows...

HTH,

 

[Michael M]

Are you trying to delete rows 3 to 80 ??
If so, try.

 Sub Macro1()
    Columns("A:A").NumberFormat = "[$-F400]h:mm:ss AM/PM"
    Rows("3:80").Delete Shift:=xlUp
    Range("K30").Select
End Sub

 

[pedro-egoli]

Thanks Michael .
No I only want to delete the info which is not at half yearly intervals. As summer approaches start time will be earlier and end time may be later

However, at this time of year he data is contained in rows 2 : 130 and covers the time from approx 6.30am to 5.10pm each day.
It is based on data collected from solar generation on a 5 minute basis.

I need info to be at half hourly intervals so as to produce a chart of the days generation.
At 5 minutes the chart is useless for comparison.

The format for sheet which is imported ( from a software programme) comes icustom formatted as “dd-mm-yy h:mm”

When I run my macro the format is simply “Time”.
The following shows how sheet looks in part before macro

Excel Workbook
	A	B
1	Date	Energy[Wh]
2	22-07-11 6:30	0
3	22-07-11 6:35	0
4	22-07-11 6:40	0
5	22-07-11 6:45	0
6	22-07-11 6:50	0
7	22-07-11 6:55	2
8	22-07-11 7:00	12
9	22-07-11 7:05	41
10	22-07-11 7:10	82
11	22-07-11 7:15	134
12	22-07-11 7:20	198
13	22-07-11 7:25	273
14	22-07-11 7:30	354
240711 chart

And after macro the sheet looks like this and the 130 rows has reduced to only 23.

Excel Workbook
	A	B
1	Date	Energy[Wh]
2	6:30:25 AM	0
3	7:00:25 AM	12
4	7:30:25 AM	354
5	8:00:25 AM	976
6	8:30:25 AM	1802
7	9:00:25 AM	2789
8	9:30:26 AM	3926
9	10:00:26 AM	5185
10	10:30:26 AM	6503
11	11:00:26 AM	7864
12	11:30:26 AM	9243
13	12:00:27 PM	10505
14	12:30:27 PM	11784
240711 chart

Sorry if my initial explanation was not clear.

Pedro

 

[pedro-egoli]

Sorry Smity,
I missed your post when I responded above.
I have no real knowledge of macros construction.
As I said in initial post, all I did was construct the macro by recording step by step the rows to be deleted.

Armed with my later post is it possible to shorten the macro .

Pedero

 

[pedro-egoli]

Bump.

In a nutshell what I need is

1. Delete rows 2:7 which are pre say 7am (time is hard to quantify as sun starts producing at various times of year) so row 8 should be the start time .
2. After row 8 delete the next 5 rows (9:13) and continue this pattern so all figures are only shown for each half hour.

Any help greatly appreciated.

Pedero

 

[p45cal]

1. Add a formula and copy it down:

Excel Workbook
	A	B	C
1	Date	Energy[Wh]
2	22-07-11 6:30	0	TRUE
3	22-07-11 6:35	0	FALSE
4	22-07-11 6:40	0	FALSE
5	22-07-11 6:45	0	FALSE
6	22-07-11 6:50	0	FALSE
7	22-07-11 6:55	2	FALSE
8	22-07-11 7:00	12	TRUE
9	22-07-11 7:05	41	FALSE
10	22-07-11 7:10	82	FALSE
11	22-07-11 7:15	134	FALSE
12	22-07-11 7:20	198	FALSE
13	22-07-11 7:25	273	FALSE
14	22-07-11 7:30	354	TRUE
Sheet

Autofilter for FALSE:

Excel Workbook
	A	B	C
1	Date	Energy[Wh]
3	22-07-11 6:35	0	FALSE
4	22-07-11 6:40	0	FALSE
5	22-07-11 6:45	0	FALSE
6	22-07-11 6:50	0	FALSE
7	22-07-11 6:55	2	FALSE
9	22-07-11 7:05	41	FALSE
10	22-07-11 7:10	82	FALSE
11	22-07-11 7:15	134	FALSE
12	22-07-11 7:20	198	FALSE
13	22-07-11 7:25	273	FALSE
Sheet

Select, right-click and delete entire rows, then remove the Autofilter and delete the column with the formulae:

Excel Workbook
	A	B
1	Date	Energy[Wh]
2	22-07-11 6:30	0
3	22-07-11 7:00	12
4	22-07-11 7:30	354
Sheet

I note later that there appears to be 25 secs, sometimes more, more than the exact half hour, try adjusting the formula from
=ROUND((A2*48),2)=ROUND((A2*48),0)
to
=ROUND((A2*48),1)=ROUND((A2*48),0)

 

[p45cal]

The trouble with the above offering is that if the times get further away than about 1min:30secs from the hour/half hour, they're missed altogether.
You could try this macro instead where you should select a single cell you want to keep near the bottom of the list of times and then run it. It will delete blocks of 5 rows above the original selected cell to the top:

Sub blah()
For rw = ActiveCell.Row To 4 Step -6
     Range(Cells(rw - 1, 1), Cells(rw - 5, 1)).EntireRow.Delete
Next rw
End Sub

 

[pedro-egoli]

Thanks for that macro.

It looks like it will work.
Unfortunately I lost the file I had been working on and have used one from today which at time of writing is only 10.30am.

The only thing I did was to format column A as Time and then I selected the last cell in column A with an entry ran macro and it worked.

Graph works to .
Is there an addition to macro that would format column A as Time?

Pedro