Select current region with empty column

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
Seamus2626Asked:
Who is Participating?
 
StephenJRConnect With a Mentor Commented:
Funny, it worked for me on your file.
0
 
StephenJRCommented:
You could try this:

Sheet1.UsedRange.Select
0
 
Seamus2626Author Commented:
Thanks Stephen,

but thats giving me a "Select Method of Range class" failed
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
AnuroopsunddCommented:
=COLUMNS((A:A)+(C:G))
0
 
Seamus2626Author Commented:
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
 
StephenJRCommented:
No need to declare anything, but probably connected to what you are doing once you select. Can you post your code?
0
 
Seamus2626Author Commented:
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
 
StephenJRCommented:
What comes after that last line?
0
 
Seamus2626Author Commented:
Selection.copy
Windows("TaskManager v2.2.xls").Activate
0
 
Seamus2626Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.