Selecting Non-contiguous cells based on active cell

T

thab

New Member
Joined
Mar 5, 2010
Messages
12
Hi :)

I need to select column A,D,E , whereas my activecell can be anywhere in column A and need to select entire cells inculding D and E(Non-contiguous).

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Emp id</TD><TD>Name</TD><TD>Address</TD><TD>DOJ</TD><TD>Depatrment</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">123</TD><TD>aaa</TD><TD>x1</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>qqq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">124</TD><TD>bbb</TD><TD>x2</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>ppp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">125</TD><TD>ccc</TD><TD>x3</TD><TD style="TEXT-ALIGN: right">6/4/2011</TD><TD>www</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">126</TD><TD>eee</TD><TD>x4</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>qqq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">127</TD><TD>fff</TD><TD>x5</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>ppp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">128</TD><TD>ggg</TD><TD>x6</TD><TD style="TEXT-ALIGN: right">6/4/2011</TD><TD>www</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">129</TD><TD>hhh</TD><TD>x7</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>qqq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">130</TD><TD>iii</TD><TD>x8</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>ppp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">131</TD><TD>ccc</TD><TD>x9</TD><TD style="TEXT-ALIGN: right">6/4/2011</TD><TD>www</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">132</TD><TD>jjj</TD><TD>x10</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>qqq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">133</TD><TD>kkk</TD><TD>x11</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>ppp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">134</TD><TD>llll</TD><TD>x12</TD><TD style="TEXT-ALIGN: right">6/4/2011</TD><TD>www</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">135</TD><TD>mmm</TD><TD>x13</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>qqq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">136</TD><TD>nnn</TD><TD>x14</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>ppp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">137</TD><TD>ooo</TD><TD>x15</TD><TD style="TEXT-ALIGN: right">6/4/2011</TD><TD>www</TD></TR></TBODY></TABLE>
Sheet1​




For example, if active cell is A8, output should be

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">129</TD><TD>hhh</TD><TD>x7</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>qqq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">130</TD><TD>iii</TD><TD>x8</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>ppp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">131</TD><TD>ccc</TD><TD>x9</TD><TD style="TEXT-ALIGN: right">6/4/2011</TD><TD>www</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">132</TD><TD>jjj</TD><TD>x10</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>qqq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">133</TD><TD>kkk</TD><TD>x11</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>ppp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">134</TD><TD>llll</TD><TD>x12</TD><TD style="TEXT-ALIGN: right">6/4/2011</TD><TD>www</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">135</TD><TD>mmm</TD><TD>x13</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>qqq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">136</TD><TD>nnn</TD><TD>x14</TD><TD style="TEXT-ALIGN: right">6/5/2011</TD><TD>ppp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">137</TD><TD>ooo</TD><TD>x15</TD><TD style="TEXT-ALIGN: right">6/4/2011</TD><TD>www</TD></TR></TBODY></TABLE>
Sheet1​


i.e based on active cell , output should be dispalyed. Please help me out to write a macro on this.

i tried out this code, but able to do half only,

Public sub copysheet()
Range(ActiveCell, ActiveCell.End(xlDown)).copy
Sheets.Add
ActiveSheet.Paste
End Sub



Thanks in advance :)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
I'm confused by your description. Your title say "Selecting Non-contiguous cells.." and your worded description says "I need to select column A,D,E..", but your sample output shows all the columns still there. What is the non-contiguous A,D,E stuff about? Or is your sample output incorrect?
 
Upvote 0
Hi :)

I need to select column A,D,E , whereas my activecell can be anywhere in column A and need to select entire cells inculding D and E(Non-contiguous).

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Emp id</TD><TD>Name</TD><TD>Address</TD><TD>DOJ</TD><TD>Dept.</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">123</TD><TD>aaa</TD><TD>x1</TD><TD style="TEXT-ALIGN: right">6/6/2011</TD><TD>qq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">124</TD><TD>bbb</TD><TD>x2</TD><TD style="TEXT-ALIGN: right">6/7/2011</TD><TD>pp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">125</TD><TD>ccc</TD><TD>x3</TD><TD style="TEXT-ALIGN: right">6/8/2011</TD><TD>zz</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">126</TD><TD>ddd</TD><TD>x4</TD><TD style="TEXT-ALIGN: right">6/9/2011</TD><TD>qq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">127</TD><TD>eee</TD><TD>x5</TD><TD style="TEXT-ALIGN: right">6/10/2011</TD><TD>pp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">128</TD><TD>fff</TD><TD>x6</TD><TD style="TEXT-ALIGN: right">6/11/2011</TD><TD>zz</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">129</TD><TD>ggg</TD><TD>x7</TD><TD style="TEXT-ALIGN: right">6/12/2011</TD><TD>qq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">130</TD><TD>hhh</TD><TD>x8</TD><TD style="TEXT-ALIGN: right">6/13/2011</TD><TD>pp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">131</TD><TD>iii</TD><TD>x9</TD><TD style="TEXT-ALIGN: right">6/14/2011</TD><TD>zz</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">132</TD><TD>jjj</TD><TD>x10</TD><TD style="TEXT-ALIGN: right">6/15/2011</TD><TD>qq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">133</TD><TD>kkk</TD><TD>x11</TD><TD style="TEXT-ALIGN: right">6/16/2011</TD><TD>pp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">134</TD><TD>lll</TD><TD>x12</TD><TD style="TEXT-ALIGN: right">6/17/2011</TD><TD>zz</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">135</TD><TD>mmm</TD><TD>x13</TD><TD style="TEXT-ALIGN: right">6/18/2011</TD><TD>qq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">136</TD><TD>ooo</TD><TD>x14</TD><TD style="TEXT-ALIGN: right">6/19/2011</TD><TD>pp</TD></TR></TBODY></TABLE>
Sheet1​






For example, if active cell is A8, output should be




Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">129</TD><TD style="TEXT-ALIGN: right">6/12/2011</TD><TD>qq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">130</TD><TD style="TEXT-ALIGN: right">6/13/2011</TD><TD>pp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">131</TD><TD style="TEXT-ALIGN: right">6/14/2011</TD><TD>zz</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">132</TD><TD style="TEXT-ALIGN: right">6/15/2011</TD><TD>qq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">133</TD><TD style="TEXT-ALIGN: right">6/16/2011</TD><TD>pp</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">134</TD><TD style="TEXT-ALIGN: right">6/17/2011</TD><TD>zz</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">135</TD><TD style="TEXT-ALIGN: right">6/18/2011</TD><TD>qq</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">136</TD><TD style="TEXT-ALIGN: right">6/19/2011</TD><TD>pp</TD></TR></TBODY></TABLE>
Sheet2​

i.e based on active cell , output should be dispalyed. Please help me out to write a macro on this.

i tried out this code, but able to do little,

Public sub copysheet()
Range(ActiveCell, ActiveCell.End(xlDown)).copy
Sheets.Add
ActiveSheet.Paste
End Sub



Thanks in advance :)
 
Upvote 0
Not exactly the approach you were expecting, but how about this...
Code: 
Sub CopyADEfromActiveCellDownward()
  Dim ActiveRow As Long
  ActiveRow = ActiveCell.Row
  Range("A" & ActiveRow & ":E" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Worksheets("Sheet2").Range("A1")
  Worksheets("Sheet2").Range("B:C").Delete
End Sub
 
Upvote 0
Code: 
Sub test()

Dim aCell As Range
Set aCell = ActiveCell
With Worksheets.Add
    Application.Intersect(Range(aCell, aCell.End(xlDown)).EntireRow, aCell.Parent.Range("A1,D1,E1").EntireColumn).Copy Destination:=.Range("A1")
End With

End Sub
 
Upvote 0
Rick Rothstein said:
Not exactly the approach you were expecting, but how about this...
Code: 
Sub CopyADEfromActiveCellDownward()
  Dim ActiveRow As Long
  ActiveRow = ActiveCell.Row
  Range("A" & ActiveRow & ":E" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Worksheets("Sheet2").Range("A1")
  Worksheets("Sheet2").Range("B:C").Delete
End Sub
Click to expand...
Originally, I was going in a different direction and thought I would be referencing the active row number more than once; but since my final code only references it once, there is no reason to declare/use a variable for this. Here is my modified code...
Code: 
Sub CopyADEfromActiveCellDownward()
  Range("A" & ActiveCell.Row & ":E" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Worksheets("Sheet2").Range("A1")
  Worksheets("Sheet2").Range("B:C").Delete
End Sub
 
Upvote 0
mikerickson said:
Code: 
Sub test()
 
Dim aCell As Range
Set aCell = ActiveCell
With Worksheets.Add
    Application.Intersect(Range(aCell, aCell.End(xlDown)).EntireRow, aCell.Parent.Range("A1,D1,E1").EntireColumn).Copy Destination:=.Range("A1")
End With
 
End Sub
Click to expand...




I altered the above code to my preference, but when active cell is A15(for example) i.e last cell then entire empty row after that also selected. Is there any way to come out of this?
 
Upvote 0
Try
Code: 
Range(aCell, aCell.EntireColumn.Cells(Rows.Count, 1).End(xlUp))
instead of
Code: 
Range(aCell, aCell.End(xlDown)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
89
Dazzybeeguy
D
D
  • Solved
Compare two ranges, highlight cell with text in one range missing from other
Replies
6
Views
729
Peter_SSs
Peter_SSs
E
  • Question Question
Adding a custom message.
Replies
1
Views
663
Micron
M

Forum statistics

Threads
1,224,564
Messages
6,179,544
Members
452,925
Latest member
duyvmex

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top