Excel help required please.

jhwjhw, Apr 22, 12:23am
Ive two data loggers, one recording every second, the other I can vary the sampling rate, its usually set at 6 seconds for 12hrs of data recording.

Id like to marry the two data fields together, but first i need a macro that removes rows of data.
The first Datalogger records in 4 column, I need a macro that
First, checks to see there is any data in R5C1
Then remove the next four rows beneath, and so on down the 50,000 rows of data.
And another macro that does the same so I can remove the next nine rows below.

Data always commences on R5C1 and is four columnsC1 to C4 of info.

So, The data fields, when macroed, will equate to the data fields in the 2nd datalogger and everything will be in sequence.

Can anyone suggest what the macro should be.


duncb, Apr 22, 1:23am
You will need to write your own macro to do this. Quite easy to do if you know how. If you need someone to do it try Macrotime Software, Albany in the white pages

jhwjhw, Apr 22, 5:00am
I think I can do its in the old XL4 macros, VB is more of a challenge though, so verbose in comparison, but yes, far more versitile.
Still looking for assistance though, please.

r.g.nixon, Apr 22, 5:25am
If your spreadsheet is in a .csv file, you could use some relatively simple programming to parse and rewrite the file. AutoIt3 could be the scripting language to use, but I'm not an expert - ask on their forums.

clip1, Apr 22, 9:13am
Just had a go at laying this little problem out. If I may, could I check the logic with you !

Data Starts in this cell Data Data Data
Delete Row
Delete Row
Delete Row
Delete Row
Next Block of dataData Data Data
Delete Row
Delete Row
Delete Row
Delete Row
Next Block of dataData Data Data


Start, define variables etc
Select cell A5
Select next 4 rows below A5
Delete those selected rows - use a cut command
Once the delete has occurred, the next cell down (with data in it) should be selected
Select next 4 rows below this cell (should be from A6 onwards)
Delete those selected rows

and so on etc for approximately 50000 rows
so this would be approximately 12500 loops (50000 rows divided by 4 rows to delete)

So enclose the routine in a loop of some kind
- perhaps a Do While there is data, or use a counter type loop.

Detecting the end could be a little tricky - just allow it to run until no more data maybe ! Or tie it in with the Do While Loop !

Is this what you are looking for in the macro !

jhwjhw, Apr 22, 7:31pm
clip1 - Yes to the data layout.

Thats why i included an If statement for the start of the macro.
=If (R5C1") > 1
Then (Select(("R[5])

Something like hat using XL4 XLM macro language.


gj, Apr 22, 10:39pm
why complicate this - you should be able to use standard Excel tools
??? select the header row, and turn on autofilter (from the data menu tab)
??? click any cell in a column containing data AND blanks
??? click the filter button at the head of that column, and select blanks only
??? you should now see all the rows with blank entries - select all blank rows as one block and press [Ctrl and - ] to delete them
??? use the filter button to select all

gj, Apr 22, 10:42pm
Another way to do this is simply to sort all data in the table and select and delete the blank rows which will now be in one block.
Use this method with caution - unless your data has a unique time entry or similar to make sure the real data stays in its original order.

jhwjhw, Apr 23, 8:48am
Hmm, the problem being, every one of the 50,000 rows, four columns deep, contains data, there are no blanks.

clip1, Apr 23, 9:39am
hacked this up in VBA. Missed the starting bit about checking that first cell for data, but other than that, this will get delete a block of 4 rows below a startingpoint of say cell A5 (row 5, column 1). There is probably a more elegant method of programming the macro, but I don't have lot of excel vba programming experience.

Sub Delete4UnwantedRows()

'Find Last Row with data, and assign the value to variable "LastRow"
With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
End With

'Position cursor in cell A6 (one row below 1st row with data in it i.e. cell A5)

'Run a loop to select row 6 and the next 3 rows, delete them, then move down 1 row
'until you get to the last row with data, then exit the loop
Counter = 1
Do Until Counter = LastRow
Range(ActiveCell, ActiveCell.Offset(3, 0)).EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Counter = Counter + 1


End Sub

wendalls, Apr 24, 9:03pm
Did you sort it! My husband reckons its easy just using filters.

owene, Apr 24, 10:56pm
Out of curiosity, what are your data loggers recording!. are they RS232 or USB and what software are you using to get from that port into Excell! Cheers.

Share this thread

Buy me a coffee :)Buy me a coffee :)