MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

how are cell coordinates referenced on a clicked cell

  Asked By: Diane    Date: Nov 21    Category: MS Office    Views: 1188

I've cobbled together a little script to thin out some data from a
digital oscilloscope. I get far too many datapoints so I wrote this
script to delete 9 out of 10 points in a series of 35,000 points.

Presently I have to increment "x" to move the macro around to thin out
various columns. I would prefer to be able to click on the top of a
column or and invoke the macro on the data below instead of
repetitively editing my macro.

Is there some way to reference the column coordinate on a cell I click
before calling the macro?

Sub Macro1()
' Macro recorded 9/13/2005 by C
' Keyboard Shortcut: Ctrl+w
x = 3
For y = 1 To 3500
For inc = 1 To 9
Cells(y + 1, x).Select
Selection.Delete Shift:=xlUp
Next inc
Next y
End Sub



8 Answers Found

Answer #1    Answered By: Topaz Ramirez     Answered On: Nov 21

I'd put in another for each .. next and move through a range.

Something like ...

Next Cel
Dim olXRange As Range
Dim Cel As Range

Set olXRange = Range("A1:B1")
For Each Cel In olXRange
x = cel.column
For y = 1 To 3500
For inc = 1 To 9
Cells(y + 1, x).Select
Selection.Delete Shift:=xlUp
Next inc
Next y

Next Cel

I've not tested this but it should be ok.

Answer #2    Answered By: Angie Bennett     Answered On: Nov 21

the code is working...but haven't got the right output..
the prob is i need to create a new column out of the given data  but still keep
the old data.
something like this:

Region(DATA) Region Summary(output)
Manila A Manila ABC
Manila B Sydney 12
Manila C
Sydney 1
Sydney 2

I need to actually have the Region Summary on a separate column but still keep
the Region column, for instance my data is in cells A1 to A6, then I'm going to
summarize the data to column...please help me on this...

Answer #3    Answered By: Ray Lawrence     Answered On: Nov 21

I think there is a little confusion between questions here. The top part
doesn't reference the question I answered in the bottom.

Answer #4    Answered By: Chad Bradley     Answered On: Nov 21

"Is there some way to reference the column coordinate on a cell  I
click before calling the macro?"

instead of x = 3 have:
x = selection.column

BTW, if you were to put:

Range(Cells(y + 1, x), Cells(y + 9, x)).Delete Shift:=xlUp

instead of:
For inc = 1 To 9
Cells(y + 1, x).Select
Selection.Delete Shift:=xlUp
Next inc

it would work faster and give you the same result (it deletes 9 cells
at a time and doesn't spend time selecting cells).

But wait... you're doing this on several columns? Are the columns all
next to each other? If so then delete  all the relevant columns at the
same time. Replace entire macro code with:

Application.ScreenUpdating = False
x = Selection.Cells(1, 1).Column
z = Selection.Columns.Count + x - 1
For y = 1 To 3500
Range(Cells(y + 1, x), Cells(y + 9, z)).Delete Shift:=xlUp
Next y
Application.ScreenUpdating = True

To use, select the columns (or a range containing the columns) you
want to trim down and run it. The ScreenUpdating thing further cuts
down processing time.

Answer #5    Answered By: Laurel Collins     Answered On: Nov 21

I managed to add moving average functionality and
an ability to end the script  on an empty cell  instead of the
arbitrary 3500 loop count.

I couldn't figure out how to sum across a range though. How do you do

I sure wished I installed the vba help files before I misplaced my
installation CD...

Application.ScreenUpdating = False

x = Selection.Cells.Column
y = Selection.Cells.Row

Do Until Len(Cells(y, x)) = 0

Cells(y, x).Value = (Cells(y, x).Value + Cells(y + 1, x).Value +
Cells(y + 2, x).Value + Cells(y + 3, x).Value) / 4

Range(Cells(y + 1, x), Cells(y + 3, x)).Delete shift:=xlUp

y = y + 1


Application.ScreenUpdating = True

Answer #6    Answered By: Orville Rodriguez     Answered On: Nov 21


Cells(y, x).Value = Application.WorksheetFunction.Sum(Range(Cells(y,
x), Cells(y + 3, x))) / 4

Answer #7    Answered By: Bonifaco Garcia     Answered On: Nov 21

x = selection.column is the answer I was looking for. Thanks for the
range hint too.

This is my first foray into VB. I spend most of my time making metal
things so I have a lot of reading to do to understand how your last
script works.

Am I to understand:

"Application.ScreenUpdating = False" disables cell  updating so Excel
doesn't go through the steps of reupdating the entire data  set?

Answer #8    Answered By: Estella Mitchell     Answered On: Nov 21

No, the cells' values are updated (vba may have to refer to them later
in the code) it just does what it says on the tin; The screen doesn't
get updated until either the vba code has finished running or a
Screenupdating=true instruction is encountered. It simply saves the
time excel spends updating the screen after every vba instruction,
which for your original code was some 35,000 times.

Didn't find what you were looking for? Find more on how are cell coordinates referenced on a clicked cell Or get search suggestion and latest updates.