Solved

Select current region with empty column

Posted on 2012-04-12
10
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

691 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