How can I remain in my current position on worksheet? My macro always
drags me to row 1 when it pastes to a cell.

Post your code

"Fan924" wrote:

> How can I remain in my current position on worksheet? My macro always
> drags me to row 1 when it pastes to a cell.
>

> How can I remain in my current position on worksheet? My macro always
> drags me to row 1 when it pastes to a cell.

Your code presumably involves selecting a range in row 1. Selecting ranges
is rarely necessary in macros. Please post your code. Did you generate it
with the recorder?

> Your code presumably involves selecting a range in row 1. Selecting ranges
> is rarely necessary in macros.

Is there another way to do it without selecting a range?

Sub CheckSum_xx()
Dim r As Range, c As Range
Dim Checksum As Variant
Checksum = 0
Range("E1").Value = "Working"
Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows
For Each c In r.Cells
Checksum = Checksum + Val("&h" & UCase(c.Text)) 'hex to
decimal, then sum
Next c
Next r
Range("E1").Value = Right(Hex(Checksum), 4)
End Sub

Try this

Sub CheckSum_xx()
Dim r As Range, c As Range, MyRange as Range
Dim Checksum As Variant
Checksum = 0
Range("E1").Value = "Working"
Set MyRange = Range("C2:C8193") 'select cells to export
For Each r In MyRange
For Each c In r.Cells
Checksum = Checksum + Val("&h" & UCase(c.Text)) 'hex to
decimal, then sum
Next c
Next r
Range("E1").Value = Right(Hex(Checksum), 4)
End Sub

Mike

"Fan924" wrote:

> > Your code presumably involves selecting a range in row 1. Selecting ranges
> > is rarely necessary in macros.
>
> Is there another way to do it without selecting a range?
>
> Sub CheckSum_xx()
> Dim r As Range, c As Range
> Dim Checksum As Variant
> Checksum = 0
> Range("E1").Value = "Working"
> Range("C2:C8193").Select 'select cells to export
> For Each r In Selection.Rows
> For Each c In r.Cells
> Checksum = Checksum + Val("&h" & UCase(c.Text)) 'hex to
> decimal, then sum
> Next c
> Next r
> Range("E1").Value = Right(Hex(Checksum), 4)
> End Sub
>

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)


"Fan924" <(E-Mail Removed)> wrote in message
news:f97c2f69-c82c-4f70-a183-(E-Mail Removed)...
>> Your code presumably involves selecting a range in row 1. Selecting
>> ranges
>> is rarely necessary in macros.
>
> Is there another way to do it without selecting a range?
>
> Sub CheckSum_xx()
> Dim r As Range, c As Range
> Dim Checksum As Variant
> Checksum = 0
> Range("E1").Value = "Working"
> Range("C2:C8193").Select 'select cells to export
> For Each r In Selection.Rows
> For Each c In r.Cells
> Checksum = Checksum + Val("&h" & UCase(c.Text)) 'hex to
> decimal, then sum
> Next c
> Next r
> Range("E1").Value = Right(Hex(Checksum), 4)
> End Sub

Thanks, works great.