macro's manipulating data

  • Thread starter Thread starter Keith Wilson
  • Start date Start date
K

Keith Wilson

When I try to write a macro manipulating sorted data, I
record and use keystrokes to get to the end of a column
(i.e. end and down key), say, row 450, and then delete
lines from beneath this row. However when I subsequently
try and run the macro on a new set of data, it
automatically goes to row 450, rather than to the bottom
of the column for the new data.

Do i have to change the instructions in Visual Basic? Why?
What to? etc.

Help!

Keith
Westminster
 
Hi
probably in your recorded macro the row_index is hard coded. Could you
post your recorded macro. Should be easy to change it accordingly
 
I would post my recorded macro but Excel just crashed and
of course I hadn't saved it, so I'm going to do something
else instead!

Thanks for your offer though, I am preety sure it was hard
coded.

Keith
 
Keith,

This code snippet shows you how to set a variable range that can then be
sorted. This code assumes that you start at A1, and you want 3 columns, just
change to suit

Dim rng As Range

Set rng = Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row, 3)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I've re-written the macro but the problem is the same. I
think it goes wrong where it says range(630:9440), as I
don't want this to be hard coded.....but what do I know!

This is the first time I've tried this newsgroup forum to
resolve problems , and I'm amazed how helpful folk are.

Thanks.

here's the macro as it stands.
Keith

wheelchair Macro
' Macro recorded 29/03/04 by Keith Wilson
'
' Keyboard Shortcut: Ctrl+w
'
Sheets("Original data").Select
Sheets("Original data").Copy After:=Sheets(3)
Sheets("Original data (2)").Select
Sheets("Original data (2)").Name = "Wheelchair"
Cells.Select
Selection.Sort Key1:=Range("S2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
ActiveWindow.LargeScroll ToRight:=1
Range("S2").Select
Selection.End(xlDown).Select
Rows("630:9440").Select
Range("M630").Activate
Selection.Delete Shift:=xlUp
ActiveWindow.LargeScroll ToRight:=-1
Range("A631").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-629]C:RC)"
Range("A632").Select
Application.CommandBars("Circular Reference").Visible
= False
Range("A631").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-629]C:R[-1]C)"
Range("B631").Select
ActiveCell.FormulaR1C1 = "@"
Range("C631").Select
ActiveCell.FormulaR1C1 = "$4 "
Range("D631").Select
ActiveCell.FormulaR1C1 = "=+RC[-1]*RC[-3]"
Range("D632").Select
End Sub
 
Hi Keith,

Do you want to delete all rows from 630 down until no more data? If so, try
this (I have removed many of the superfluous selects)

Sub wheelchair()

Sheets("Original data").Copy After:=Sheets(3)
Sheets("Original data (2)").Select
Sheets("Original data (2)").Name = "Wheelchair"
Cells.Sort Key1:=Range("S2"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Rows("630:" & Cells(Rows.Count, "A").End(xlUp).Row).Delete Shift:=xlUp
Range("A631").FormulaR1C1 = "=COUNTA(R[-629]C:RC)"
Application.CommandBars("Circular Reference").Visible = False
Range("A631").FormulaR1C1 = "=COUNTA(R[-629]C:R[-1]C)"
Range("B631").FormulaR1C1 = "@"
Range("C631").FormulaR1C1 = "$4 "
Range("D631").FormulaR1C1 = "=+RC[-1]*RC[-3]"
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

keith said:
I've re-written the macro but the problem is the same. I
think it goes wrong where it says range(630:9440), as I
don't want this to be hard coded.....but what do I know!

This is the first time I've tried this newsgroup forum to
resolve problems , and I'm amazed how helpful folk are.

Thanks.

here's the macro as it stands.
Keith

wheelchair Macro
' Macro recorded 29/03/04 by Keith Wilson
'
' Keyboard Shortcut: Ctrl+w
'
Sheets("Original data").Select
Sheets("Original data").Copy After:=Sheets(3)
Sheets("Original data (2)").Select
Sheets("Original data (2)").Name = "Wheelchair"
Cells.Select
Selection.Sort Key1:=Range("S2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
ActiveWindow.LargeScroll ToRight:=1
Range("S2").Select
Selection.End(xlDown).Select
Rows("630:9440").Select
Range("M630").Activate
Selection.Delete Shift:=xlUp
ActiveWindow.LargeScroll ToRight:=-1
Range("A631").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-629]C:RC)"
Range("A632").Select
Application.CommandBars("Circular Reference").Visible
= False
Range("A631").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-629]C:R[-1]C)"
Range("B631").Select
ActiveCell.FormulaR1C1 = "@"
Range("C631").Select
ActiveCell.FormulaR1C1 = "$4 "
Range("D631").Select
ActiveCell.FormulaR1C1 = "=+RC[-1]*RC[-3]"
Range("D632").Select
End Sub



-----Original Message-----
Hi
probably in your recorded macro the row_index is hard coded. Could you
post your recorded macro. Should be easy to change it accordingly

--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
Click to expand...
 
Back
Top