Solved

Select current region with empty column

Posted on 2012-04-12
10
350 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
Industry Leaders: 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

Technology Partners: 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!

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

685 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