I'm sure there is a way...

  • Thread starter Thread starter GMB_Excel
  • Start date Start date
G

GMB_Excel

Hello everyone!

This is my first visit here and I hope to get an answer on something
am trying to get done in Excel. A look through other posts have als
made me realise how much more can be done in Excel than I eve
thought...

Here's my situation: I am a physiologist collecting data from a
ultrasound machine on the diameter of blood vessels. When my experimen
is finished I end up with a file that has four columns and up to 23,00
rows. The important columns are the first two and they are a tim
column (format: hh:mm:ss), and a diameter column. The frequency o
measurements is such that for every second you end up with 25 rows, bu
unfortunately, sometimes you end up with 24 rows in a second. What
need to do is to get average diameters for every second and for ever
ten seconds.

What I have done so far is to use "if" statements to give all the row
within a second a marker (so that "10:25:01" is "1", "10:25:02" is "2"
etc.), and then average the diameters for each of these markers (usin
more "if" statements to add the diameters within a marker and divide b
the "count" of the marker). Having done this I end up with a colum
where I get the average diameters I need, but with 24 blank cells i
between. That's where the problems begin.

Problem 1: I get an error message saying that a 2D plot can onl
contain up to 16,000 points for each series. This is despite filterin
the column with the results to only display the "nonblanks" which are
lot less than 16,000.

Problem 2: When I average the one-second intervals to get ten-secon
intervals, and then try to filter this column to only displ
"nonblanks" my averages are messed up because the source column ha
changed as well.

My questions:

1. Is there any way to average for every so many rows? If yes, is ther
a way to quickly have an automatic check to see if every second has 2
rows to it? If I cannot is there a way I can work around th
possibility of having a few seconds that will contain a differen
number of rows?

2. When you have a column with blank cells, is it possible to copy i
and then paste only the nonblank cells? (this could prevent the erro
message I get and which pauses my macro).

I appreciate this is a long, and possibly unclear explanation so I wil
not be too surprised if you ignore me, but thanks in advance anyway.

All the very best,
Georg
 
OK, here comes another question to follow up from the previous one.

If you write a macro and you need to specify a range of cells that
might change between different files, can you actually specify a range
that goes from your starting cell to the "end" (last nonblank cell in
range)?

George
 
One way:

Dim rRng As Range
Set rRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
 
OK, thanks. But how do I use this if I don't write my macro in visual
basic? I only know how to record a macro and then edit it (to a certain
extent). So, this is what my macro looks like (part of it) when I copy a
formula to the whole column by using the autofill function:

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:B3"),
Type:=xlFillDefault
Range("A3:B3").Select
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Range("B4").Select
ActiveCell.FormulaR1C1 = "1"
Range("B5").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]-R[-1]C[-1]=0,R[-1]C,R[-1]C+1)"
Range("B5").Select
Selection.AutoFill Destination:=Range("B5:B22135")
Range("B5:B22135").Select


It is the "if" statement that I want to copy to the end of my data. How
would I modify this macro to do it?

Thanks a lot for your help.

George
 
Near the end of your Sub, the following lines:
'------------------------
Selection.AutoFill Destination:=Range("B5:B22135")
Range("B5:B22135").Select

'------------------------
becomes:
'------------------------
Selection.AutoFill Destination:=Range("B5",Cells(Rows.count,"B").end(xlup))
'------------------------

(you can delete the "Range("B5:B22135").Select" line

HTH
--
AP
"GMB_Excel" <[email protected]> a écrit
dans le message de
OK, thanks. But how do I use this if I don't write my macro in visual
basic? I only know how to record a macro and then edit it (to a certain
extent). So, this is what my macro looks like (part of it) when I copy a
formula to the whole column by using the autofill function:

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:B3"),
Type:=xlFillDefault
Range("A3:B3").Select
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Range("B4").Select
ActiveCell.FormulaR1C1 = "1"
Range("B5").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]-R[-1]C[-1]=0,R[-1]C,R[-1]C+1)"
Range("B5").Select
Selection.AutoFill Destination:=Range("B5:B22135")
Range("B5:B22135").Select


It is the "if" statement that I want to copy to the end of my data. How
would I modify this macro to do it?

Thanks a lot for your help.

George




JE said:
One way:

Dim rRng As Range
Set rRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Click to expand...
Click to expand...
 
Back
Top