Issue: Blank fields are being copied

[ARIF0204]

Hi, I am using the following code to copy paste information from one worksheet to other. It is supposed to filter for non-blanks in a column and copy over a range of other columns including the column that was looked up to another worksheet in certain cells (skipping 1 row and 26 columns or i.e, in AA column).

The problem I am having is in the first case the blank records are being picked and copied over. The second one is working fine (no blank is copied over).

Can't find the issue here. Help please?


Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = Worksheets("Master Sheet")
Sheets("Prioritization").Activate

Application.DisplayAlerts = False

'structural item#1
Columns("DG:DG").Select
Selection.AutoFilter
ws.Range("$DG$1:$DG$65535").AutoFilter Field:=1, Criteria1:="<>" 'filters for non blanks in DG column
ws.Range("A2:DM6500").SpecialCells(xlCellTypeVisible).Copy 'copies information up to DM column
Sheets("Prioritization").Select
Range("A65536").End(xlUp).Offset(1, 26).PasteSpecial Paste:=xlPasteValues 'skips A to Z rows and pastes first record in in AA row
Application.CutCopyMode = False
ws.AutoFilterMode = False

'structural item #2
ws.Select
Columns("DN:DN").Select
Selection.AutoFilter
ws.Range("$DN$1:$DN$65535").AutoFilter Field:=1, Criteria1:="<>"
ws.Range("A2:DT6500").SpecialCells(xlCellTypeVisible).Copy 'the numbers "6500" in this row need to be adjusted manually
Sheets("Prioritization").Select
Range("AA65536").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues 'leaves a row and appends records in next row onward in AA
Application.CutCopyMode = False
ws.AutoFilterMode = False

End Sub

 

[Rick Rothstein]

If the contents of Columns DG and DN are constants only (that is, no formulas), the you should be able to use this simpler code (it is only two code lines, although each is somewhat lengthy)...

Sub OnlyCopyDataInColumnsDGandDN()
  Sheets("Master Sheet").Columns("DG").SpecialCells(xlConstants).EntireRow.Copy _
      Sheets("Prioritization").Cells(Rows.Count, "A").End(xlUp).Offset(1)
  Sheets("Master Sheet").Columns("DN").SpecialCells(xlConstants).EntireRow.Copy _
      Sheets("Prioritization").Cells(Rows.Count, "A").End(xlUp).Offset(1)
End Sub

 

[ARIF0204]

Thank you for your reply Rick.

One thing though, I am not just copying from DG. I intend to copying information that appears in A2 to the last item in DM2 (can be upto 6500) where DG2 is non-blank from the Master Sheet to the Prioritization Sheet. DG is one item of what I am copying but plays the critical role in deciding what I need to copy (same is true for DN and I am copying up to DT). After the first section is copied based on DG, a row is left alone and then the next copying is processed (based on DN).

Both the worksheets have header rows that is why I am starting at row 2.

At the end the pasted result should look like below:

Basic Info (columns) -- (Item1 info columns) (Item2 info columns)

Basic Info (up to DF) -- DG -- (to) DM
Basic Info (up to DF) -- DG -- (to) DM DN -- (to) DT

I believe this gives a better picture of what I am doing.