Results 1 to 6 of 6
  1. #1
    Junior Member
    Join Date
    Apr 2013
    Posts
    23

    Default Excel - Visual basic request

    Ive thousands of rows of data, in an excel spreadsheet, but I only need every 6th row of info.

    The data is in four column, commencing at R5C1.

    Obviously, the first command is to check IF there is data in R5C1
    THEN delete the next 5 rows.
    ELSE.END

    Well, thats how I think I would write it in XL4 macros, but Im not sure.
    And im useless in VB

    Can somebody please assist.

  2. #2
    Junior Member
    Join Date
    Apr 2013
    Posts
    23

    Default Re: Excel - Visual basic request

    Well, its not very elegant, but Ive found a quick and dirty way of doing it.
    In a new column, number each row like this (remember I want the first of every six rows)
    1
    xx
    xx
    xx
    xx
    xx

    Select these rows, then Autofill down the 50,000 rows
    the nice thing about this is Row7 starts of with 2, then xx xx xx xx xx 3 xx xx, you get the picture
    Then, select All the data rows and columns
    Sort by the newly installed xx column
    Now I have every 6th row sequentially numbered, with the unwanted xx rows at the very bottom of the table
    Select these xx rows and delete

    Achieved the desired outcome.

    Gee, I could even write an XL4 macro to do that...

  3. #3
    Mostly harmless member kingdragonfly's Avatar
    Join Date
    Dec 2005
    Location
    Wellington NZ
    Posts
    1,066

    Default Re: Excel - Visual basic request

    clever solution jhw

  4. #4
    Junior Member
    Join Date
    Apr 2013
    Posts
    23

    Default Re: Excel - Visual basic request

    Ahem, you think thats clever.

    I once made a 3d Vlookup, table and very clever it was too.
    Imagine, three parameters, in planes X, Y & Z (depth)
    You can use VLookup to get a value out of of that plane, then use that as a reference to the Z plane (or layer) behind.

    Lateral thinking, even our engineers didnt think of that.

    I once saw a Vlookup that would give you an intermediate value, proportional to the initial input and where that fell in the table. I couldnt make that work. It was published in a 1990's issie of PC magazine, the US edition.

  5. #5
    bon vivant
    Join Date
    Apr 2005
    Posts
    47

    Default Re: Excel - Visual basic request

    Quote Originally Posted by jhw View Post
    Ive thousands of rows of data, in an excel spreadsheet, but I only need every 6th row of info.

    The data is in four column, commencing at R5C1.

    Obviously, the first command is to check IF there is data in R5C1
    THEN delete the next 5 rows.
    ELSE.END

    Well, thats how I think I would write it in XL4 macros, but Im not sure.
    And im useless in VB

    Can somebody please assist.
    If you want to try VBA, something like the following might do it reasonably quickly
    Code:
    Sub every6th()
    Dim a, c&, i&, j&
    a = Range(Cells(5, 1), Cells(Rows.Count, 1).End(3)).Resize(, 4)
    For i = 1 To UBound(a, 1) Step 6
        c = c + 1
        For j = 1 To 4
            a(c, j) = a(i, j)
        Next j
    Next i
    Range("A5").CurrentRegion.Resize(, 4).ClearContents
    Range("A5").Resize(c, 4) = a
    End Sub

  6. #6
    Junior Member
    Join Date
    Apr 2013
    Posts
    23

    Default Re: Excel - Visual basic request

    Well, Im not that great in VB macros, so here is the working version in XLM

    Here is the successful macro, written in XLM for Office 95. Though I have 55000 rows of data, XL95 only looks at 16384 rows. It matters not as the Sorting is done in Office 2003 which accepts something like 64,000 rows. All that needs changed then are the Autofill and Sort row numbers to suit.

    Six Seconds
    =SELECT("R2C1")
    =FORMULA("1")
    =SELECT("R3C1")
    =FORMULA("xx")
    =FILL.AUTO("RC:R[4]C",0)
    =SELECT("R2C1:R7C1")
    =FILL.AUTO("RC:R[16382]C",0)
    =SELECT("R2C1:R16384C6")
    =SORT(1,"R2C1",1,,,,,2,1,FALSE)
    =SELECT("R2C1")
    =FORMULA.FIND("xx",1,2,1,1,FALSE)
    =SELECT("R[]C1:R16384C6")
    =CLEAR(3)
    =SELECT("R2C3")
    =RETURN()

Similar Threads

  1. Replies: 1
    Last Post: 16-01-2008, 01:57 PM
  2. Replies: 0
    Last Post: 12-09-2000, 12:54 PM
  3. excel visual basic macro
    By in forum PressF1
    Replies: 0
    Last Post: 03-09-1999, 04:18 AM
  4. Excel, Visual Basic
    By in forum PressF1
    Replies: 0
    Last Post: 20-06-1999, 09:37 PM
  5. Excel - Visual Basic
    By in forum PressF1
    Replies: 0
    Last Post: 12-03-1999, 12:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •