Solved

Select current region with empty column

Posted on 2012-04-12
10
337 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

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…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

831 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