Range Selection Question

[mcnealfbm]

I have written a macro that will filter data in one column and then will copy and paste various columns into another worksheet. I have it working perfectly when I select the data using Range(Selection, Selection.End(xlDown)).Select

But occasionally some columns have some blanks in them and I need all of the data in that column up until the last row of data in the table.

Here is a snapshot of my code. The first part is where I tell it to filter when the column U (or 26) has the word EXPIRE. Then I have it look for a column that is labeled "RPC ID". It is that column that I want VBA to pick all of the values in that column including any blank cells.

ActiveSheet.Range("$A$2:$AA$667").AutoFilter Field:=26, Criteria1:="EXPIRE"

Cells.Find(What:="RPC Id", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Select
RowCount2 = ActiveSheet.UsedRange.Rows.Count (I created this hoping I could use it to select the bottom of the range to copy but can't get the syntax to work)

Range(Selection, Selection.End(xlDown)).Select (this works as long as there are no blanks)
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

The way I've written this is that it will select the column heading RPC ID along with the data in the column which I don't actually need but I just wrote in the code to delete it after the fact.

Any help would be appreciated!
Frances

 

[bensonsearch]

Hi,
I would add another filter straight after the firt that removes blank fields. the criteria would be ="<>"

 

[mcnealfbm]

THanks for the suggestion but I actually need to copy the blank cells as well. I figured it out by finding the code to allow me to find the top row and the bottom row and defining the range using those variables vs xlDown.

My final code is this.

Cells.Find(What:="Route Changes", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

ActiveCell.Select
CellName = ActiveCell.Address
ColNum = ActiveCell.Column

ActiveSheet.range("$A$2:$AA$667").AutoFilter Field:=ColNum, Criteria1:="EXPIRE"
Cells.Find(What:="RPC Id", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Select
CellName = ActiveCell.Address
ColNum4 = ActiveCell.Column
If ColNum4 > 26 Then n = 2 Else n = 1
Col = Mid(CellName, 2, n)
row = ActiveCell.row
RowCount2 = ActiveSheet.UsedRange.Rows.Count

range(Col & row + 1, Col & RowCount2).Select

Selection.SpecialCells(xlCellTypeVisible).Select