Forum Home
Press F1
Thread ID: 130965	2013-04-22 06:16:00	Excel - Visual basic request	jhw (17062)	Press F1

Post ID	Timestamp	Content	User
1338001	2013-04-22 06:16:00	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.	jhw (17062)
1338002	2013-04-22 08:25:00	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...	jhw (17062)
1338003	2013-04-23 00:54:00	clever solution jhw	kingdragonfly (309)
1338004	2013-04-23 08:47:00	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.	jhw (17062)
1338005	2013-04-23 11:11:00	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 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	rugila (214)
1338006	2013-04-28 00:35:00	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()	jhw (17062)
1