how to select a column

dd*

New Member
Joined
May 20, 2011
Messages
10
2 questions:

I perform a search.
in the current active cell, I would like to select that column.
how do I select the whole column?


whats happening is its selecting the entire spreadsheets data

any ideas?

many thanks

dd
ps. you would think this was easy
 
Last edited:

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
If you're asking how to select the active cell's column in VBA...

Code:
ActiveCell.EntireColumn.Select

I couldn't figure out what the 2nd question was?
 

dd*

New Member
Joined
May 20, 2011
Messages
10
thanks AlphaFrog


the 2nd question was:

if I used code to select only a column, the result is it selects the entire spreadsheet data,
any ideas?

for example
my cursor is in E1

Ive just used the code:
ActiveCell.EntireColumn.Select


the result is:
it selects the entire spreadsheet

the correct behaviour is:
only select colurmn E

any ideas?

many thanks

dd
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384

ADVERTISEMENT

If any cell in column E is merged with other cells in other columns, selecting column E will also select the columns of the merged cells.
 

cbrf23

Board Regular
Joined
Jun 20, 2011
Messages
241

ADVERTISEMENT

Merged cells can be a headache in VBA. Try to use the horizontal formatting option "Center Across Selection" instead....It looks like a merged cell, but all the cells remain seperate.

Now if they only had "Left Across Selection" or "Right Across Selection" and center,top, and bottom across selection for vertical formatting, you would could get away from merged cells entirely.
 

dd*

New Member
Joined
May 20, 2011
Messages
10
thanks cbrf23

im a vba, vb, vb.net developer however new to the excel application.

This is my working code to select a column.
although i feel i need an if statement - if searchText found etc...

many thanks again

dd

Code:
[FONT=Courier New]dim i, LastCol
Dim searchText As String
searchText = "GL Code"

''insert column A
range("A:A").Insert Shift:=xlToLeft

range("A1").Select
Cells.Find(What:=searchText, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    
columnNumber = ActiveCell.Column
columnLetter = Split(Columns(columnNumber).Address(), "$")(2)

i = columnNumber
Cells(1, i).EntireColumn.Copy
Columns("A:A").Select
ActiveSheet.Paste
Cells(1, i).EntireColumn.Delete[/FONT]
 

alvinwlh

Active Member
Joined
Feb 16, 2009
Messages
305
Can try

Code:
dim i, LastCol
Dim searchText As String
searchText = "GL Code"
 
''insert column A
range("A:A").Insert Shift:=xlToLeft
 
range("A1").Select
Cells.Find(What:=searchText, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
 
columnNumber = ActiveCell.Column
columnLetter = Split(Columns(columnNumber).Address(), "$")(2)
 
i = columnNumber
[COLOR=red]Columns("A:A").value = Columns(i).value[/COLOR]
[COLOR=red]Columns(i).Delete[/COLOR]
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
Thanks for the advise cbrf, I have stayed away from merging cells due to the problems it causes with script, I never knew that you could centre across selection, amazing the small things we miss.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,948
Members
414,417
Latest member
Nobu

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