?
Solved

Select current region with empty column

Posted on 2012-04-12
10
Medium Priority
?
369 Views
Last Modified: 2012-04-12
Hi,

I have attached a ss where i am trying to select the current region. i cannot use current selection due to the empty column(B)

How can i select this data.

The columns stay the same but the rows are variable.

I cannot simply use Columns("A:G").select as i am appending to another workbook

Thanks
Seamus
test.xls
0
Comment
Question by:Seamus2626
  • 5
  • 4
10 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 37836796
You could try this:

Sheet1.UsedRange.Select
0
 

Author Comment

by:Seamus2626
ID: 37836842
Thanks Stephen,

but thats giving me a "Select Method of Range class" failed
0
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37836851
=COLUMNS((A:A)+(C:G))
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 37836856
Funny, it worked for me on your file.
0
 

Author Comment

by:Seamus2626
ID: 37836871
I have this running in the middle of code, must i declare anything? I changed my sheet name to sheet1 to see was that the problem but its not.......
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37836877
No need to declare anything, but probably connected to what you are doing once you select. Can you post your code?
0
 

Author Comment

by:Seamus2626
ID: 37836899
Sub Getdata()

Windows("frmTransEnq.xls").Activate

Columns("A:E").delete
Columns("B:D").delete
Columns("C:C").delete
Columns("E:L").delete
Columns("F:F").delete
Columns("G:O").delete
Columns("H:K").delete
Columns("I:BC").delete
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").delete
Rows("1:1").delete
   
Sheet1.UsedRange.Select

---------------

Thanks
Seamus
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37836906
What comes after that last line?
0
 

Author Comment

by:Seamus2626
ID: 37836909
Selection.copy
Windows("TaskManager v2.2.xls").Activate
0
 

Author Closing Comment

by:Seamus2626
ID: 37836929
Anuroop, i couldnt use your solution as it would select entire columns, thank you however

Used this Stephen and all goood

ActiveSheet.UsedRange.Select

Thanks
Seamus
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question