Selecting and Copying Cells in Range if not blank AND are not hidden

328iXdrive

New Member
Joined
Sep 30, 2016
Messages
3
Hey Folks!

I'm trying to select and then copy cells within a range if a there is data in a specific column. If there is, I need it to copy all those cells in that row range even if it has some blanks in other cells in that range. I was trying this:

Code:
Sub CopyPt6()
    
    If ActiveCell = "" Then
    
    
    
    Selection.Copy
    
    ElseIf ActiveCell.Value <> "" Then
    
Range("D2:O500").Select


Selection.SpecialCells(xlCellTypeConstants).Select


Selection.SpecialCells(xlCellTypeVisible).Select


Selection.Copy
  
End If
    
End Sub

But it tells me I get Run-time error 1004: Command cannot be used on multiple selections.

I know that the problem is that it is deselecting blank cells within that range, however I need it to only deselect anything in column D is blank (and then from there select only the visible cells), so it is trying to not copy the blank cells in that range even if there is value in column D and it is not hidden. I've checked many other threads but I can't find a problem quite like this one. A lot of them get into selecting visible cells, but none of them get into how to copy if both conditions are met, visible cells AND not blank in a certain column (but other blanks in other columns on that row are OK and should be copied).

Any help is MUCH appreciated!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Can't quite figure out what you are exactly trying to do, but I think you may want a loop code to go through each row to check for value and not hidden.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

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
Top