Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default VBA: Column Select then Data Select then return to cell A1

How do you write in VBA:-
to select a column then select the range of data held in the column (say B1)
then return to Cell a1. The data in the column can change in the workbook as
it is updated regulary, so the range of data in the column varies constantly.
There are column headings so the range in column B1 must start at B2.

Many thanks
--
James
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default VBA: Column Select then Data Select then return to cell A1

Hi,

It's not entirely clear what you want but maybe this which copies the used
range of column B and pastes it into A2

Sub marine()
Dim LastRow As Long
Set sHt = Sheets("Sheet1")'Change to suit
LastRow = sHt.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Range("B2:B" & LastRow).Copy _
Destination:=sHt.Range("A2")
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"James C" wrote:

How do you write in VBA:-
to select a column then select the range of data held in the column (say B1)
then return to Cell a1. The data in the column can change in the workbook as
it is updated regulary, so the range of data in the column varies constantly.
There are column headings so the range in column B1 must start at B2.

Many thanks
--
James

  #3   Report Post  
Posted to microsoft.public.excel.misc
Rob Rob is offline
external usenet poster
 
Posts: 718
Default VBA: Column Select then Data Select then return to cell A1

Try
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select

Don't know what you want to do with the data you have selected ?

"James C" wrote:

How do you write in VBA:-
to select a column then select the range of data held in the column (say B1)
then return to Cell a1. The data in the column can change in the workbook as
it is updated regulary, so the range of data in the column varies constantly.
There are column headings so the range in column B1 must start at B2.

Many thanks
--
James

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default VBA: Column Select then Data Select then return to cell A1

Hi James

Do you mean selection of the range A1:Bx where x is variable.....If so try
the below

Range("A1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Select

--
Jacob


"James C" wrote:

How do you write in VBA:-
to select a column then select the range of data held in the column (say B1)
then return to Cell a1. The data in the column can change in the workbook as
it is updated regulary, so the range of data in the column varies constantly.
There are column headings so the range in column B1 must start at B2.

Many thanks
--
James

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
select max value from row of data and column containing max value Jesse Excel Worksheet Functions 2 June 29th 09 07:00 PM
Formula to select every third cell in a very long column of data? jme951 Excel Discussion (Misc queries) 2 November 21st 07 08:17 PM
Using formulas to select cells (Ex: Select every nth cell in a col Lakeview Photographic Services Excel Discussion (Misc queries) 2 March 15th 07 03:17 PM
Select cell, Copy it, Paste it, Return to Previous cell spydor Excel Discussion (Misc queries) 1 December 30th 05 02:29 PM
Select cell containing specific text &return value from another ce plf100 Excel Worksheet Functions 4 November 16th 05 02:57 PM


All times are GMT +1. The time now is 07:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"