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:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top