microsoft excelmicrosoft-excel-2003spreadsheet
I have an Excel spreadsheet with thousands of rows. I want to select every 7th row from that spreadsheet. (By "select," I mean delete all of the other rows, or copy the selected rows into a new worksheet.)
What's the easiest way to do that in Excel 2003?
See also: Select each nth row in Numbers
This will do what you want:
Open the Registry Editor, browse to HKEY_CLASSES_ROOT\Excel.Sheet.8\shell\Open
Delete the ddeexec key, (or just rename it if you are worried - I just renamed it and it worked)
Click on the "command" key and replace the /e on the end of the (Default) and command strings in that key with "%1"
The quotes around %1 are important and must be added.
After the change the lines should look like this:
(Default) REG_SZ "C:\Program Files (x86)\Microsoft Office\Office11\EXCEL.EXE" "%1
command REG_MULTI_SZ ']gAVn-}f(ZXfeAR6.jiEXCELFiles>!De@]Vz(r=f`1lfq`?R& "%1"
Make the same changes to Excel.Sheet.12
Now both .xls and .xlsx should open in new windows with no errors.
Here's a macro that creates a conditional format for each row in your selection. It does this by copying the format of the first row to EACH row in the selection (one by one, not altogether). Replace B1:P1 with the reference to the first row in your data table.
Sub NewCF()
Range("B1:P1").Copy
For Each r In Selection.Rows
r.PasteSpecial (xlPasteFormats)
Next r
Application.CutCopyMode = False
End Sub
To use, highlight the un-formatted rows in your dataset (in my case, B2:P300) and then run the macro. In the example below, note that the max numbers in the first two rows are 5 and 15, respectively; both cells are dark red.
I'm sure there's a faster solution than this, though.
Best Answer