excel macro question

Reply Subscribe

Thread Tools

Search this Thread

Dec 15, 2005 | 02:31 PM

Gareth Allan

Thread Starter

Scooby Regular

Joined: Jul 2001

Posts: 226

Likes: 0

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

Dec 16, 2005 | 05:05 AM

markr1963

Scooby Regular

Joined: Jun 2002

Posts: 1,866

Likes: 0

From: Perth, Western Australia

This seems to work

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

HTH
Mark

Dec 16, 2005 | 09:23 AM

Gareth Allan

Thread Starter

Scooby Regular

Joined: Jul 2001

Posts: 226

Likes: 0

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

Dec 16, 2005 | 11:31 AM

markr1963

Scooby Regular

Joined: Jun 2002

Posts: 1,866

Likes: 0

From: Perth, Western Australia

Try this...

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

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

Dec 16, 2005 | 11:57 AM

Gareth Allan

Thread Starter

Scooby Regular

Joined: Jul 2001

Posts: 226

Likes: 0

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