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
Solved

Select current region with empty column

Posted on 2012-04-12
10
342 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

837 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