excel macro question
#1
excel macro question
I have a large excel sheet, with data stored against time and date.
I have a set of readings every few seconds, although the time between readings is not always constant.
I want to filter the list from 32000 samples to around 3200, for instance.
I tried to create a macro to start at the line 1, then select line 2-99 and delete them, moving the data up. Then move down one line (which would have been line 100) and then select the next 99 lines for deletion etc, until it runs out of lines.
Can anyone work out how to do this - I cant seem to get the references to cells via the cursur position as opposed to A1 type references.
Gareth
I have a set of readings every few seconds, although the time between readings is not always constant.
I want to filter the list from 32000 samples to around 3200, for instance.
I tried to create a macro to start at the line 1, then select line 2-99 and delete them, moving the data up. Then move down one line (which would have been line 100) and then select the next 99 lines for deletion etc, until it runs out of lines.
Can anyone work out how to do this - I cant seem to get the references to cells via the cursur position as opposed to A1 type references.
Gareth
#2
Scooby Regular
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes
on
0 Posts
This seems to work
HTH
Mark
Code:
Dim a As Integer ' start point Dim b As Integer ' number of rows to be deleted Dim c As Integer ' actual number of rows deleted Dim d As Integer ' total rows Dim i As Integer ' first loop Dim j As Integer ' second loop Worksheets("Sheet1").Activate areaCount = Selection.Areas.Count a = 1 ' first row b = 99 ' number of rows to zap d = Selection.Rows.Count For i = 1 To Int(d / b) + 1 ' loops through for (rows to be deleted / number of rows) + 1 If a = 1 Then ' if it's the first row just use number of rows to be deleted c = b Else ' if any other row use rows to be deleted plus row number of the last row kept c = b + a End If For j = a + 1 To c 'loops from last kept row + 1 to total rows to be deleted Worksheets("Sheet1").Rows(a + 1).Delete ' deletes row below Next j a = a + 1 'changes starting point Next i Range("A1").Select ' returns cursor to top left
Mark
#3
thanks for that
I have a header row, so changed a = 2
it does not loop tho, is the next i supposed to do that?
running through it step by step, it goes straight to the for j loop and takes out the 99 lines there, nothing before.
BTW, Perth still nice to live in? how close to northbridge
(go and email me a case of redback please)
Gareth
I have a header row, so changed a = 2
it does not loop tho, is the next i supposed to do that?
running through it step by step, it goes straight to the for j loop and takes out the 99 lines there, nothing before.
BTW, Perth still nice to live in? how close to northbridge
(go and email me a case of redback please)
Gareth
#4
Scooby Regular
Join Date: Jun 2002
Location: Perth, Western Australia
Posts: 1,866
Likes: 0
Received 0 Likes
on
0 Posts
Try this...
Perth's still a great place to live I live in Clarkson, about 35 mins north of the CDB, so not far from Northbridge.
Here ya go
Code:
Dim a As Integer ' start point Dim b As Integer ' number of rows to be deleted Dim c As Integer ' actual number of rows deleted Dim d As Integer ' total rows Dim i As Integer ' first loop Dim j As Integer ' second loop Worksheets("Sheet1").Activate areaCount = Selection.Areas.Count a = 2 ' first row b = 100 ' number of rows to zap d = Selection.Rows.Count For i = 1 To Int(d / b) + 1 ' loops through for (rows to be deleted / number of rows) + 1 If a = 2 Then ' if it's the first row just use number of rows to be deleted c = b Else ' if any other row use rows to be deleted plus row number of the last row kept c = a + b - 1 End If For j = a + 1 To c 'loops from last kept row + 1 to total rows to be deleted Worksheets("Sheet1").Rows(a + 1).Delete ' deletes row below Next j a = a + 1 'changes starting point Next i Range("A1").Select
Here ya go
#5
thanks for that, very refreshing! (sitting here and its snowing outside...)
still not quite right - can i email you a sample excel sheet so you see what I mean?
Gareth
still not quite right - can i email you a sample excel sheet so you see what I mean?
Gareth
Thread
Thread Starter
Forum
Replies
Last Post
Mattybr5@MB Developments
Full Cars Breaking For Spares
28
28 December 2015 11:07 PM
Mattybr5@MB Developments
Full Cars Breaking For Spares
12
18 November 2015 07:03 AM
Brzoza
Engine Management and ECU Remapping
1
02 October 2015 05:26 PM