select an entire row

f0h

New Member
Joined
Nov 2, 2002
Messages
14
how would I write the line of code that would select the entire row that the active cell is in?
Thanx
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,934
On 2002-11-03 03:15, f0h wrote:
how would I write the line of code that would select the entire row that the active cell is in?
Thanx
Welcome to the board,

ActiveCell.EntireRow.Select

Eli
 

Hugo

New Member
Joined
Oct 10, 2002
Messages
13
eliw,

Beat me to it! I was going to say that that’s what I use.

But what I actually want to do is select the cells in columns B1 to Q1 when B1 is the active cell.

Any ideas how to modify the above?
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
Here is the line of code to do that, for this example housed in a worksheet selection change event procedure.

Right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
Range("B1:Q1").Select
End Sub

Any help?
 

Hugo

New Member
Joined
Oct 10, 2002
Messages
13

ADVERTISEMENT

Tom,

Many thanks for your reply. Not quite what I am looking for though – my query was a bit too simplified.

Here is an extract from a rather large macro that I run – which updates the workbook daily.

Sheets("Input Data").Select
Range("B65536").End(xlUp).Select
ActiveCell.EntireRow.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
NextRow = Range("B65536").End(xlUp).Row + 1
Range("B" & NextRow).Select
Sheets("Output Summary").Select

You will see that I copy the entire new row and paste special with the values.

What I would like to do is amend line 3 in this macro extract to select and paste the values in columns B to Q only and not the whole row as at present.

(The reason behind this is that Column A contains dates, and B to Q contain formulae. If I did not copy and paste the values in B to Q then they would change on each daily update and thus show the wrong data (today’s data) for previous dates.)
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
Try replacing the following 3 lines of your code:

ActiveCell.EntireRow.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



...with this 1 line instead:

Range(ActiveCell, ActiveCell.Offset(0, 15)).Value = Range(ActiveCell, ActiveCell.Offset(0, 15)).Value
 

Hugo

New Member
Joined
Oct 10, 2002
Messages
13
Tom,

Many thanks indeed for that – works just how I want – I would never have worked that out for myself!
 

Forum statistics

Threads
1,144,293
Messages
5,723,554
Members
422,503
Latest member
aarifmahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top