Selecting Non-contiguous cells based on active cell

[thab]

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

 

[Rick Rothstein]

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?

 

[thab]

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

 

[Rick Rothstein]

Not exactly the approach you were expecting, but how about this...

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

 

[mikerickson]

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

 

[Rick Rothstein]

Not exactly the approach you were expecting, but how about this...

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

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...

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

 

[thab]

Thanks Boss it worked.

 

[thab]

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

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?

 

[mikerickson]

Try

Range(aCell, aCell.EntireColumn.Cells(Rows.Count, 1).End(xlUp))

instead of

Range(aCell, aCell.End(xlDown)