About Us

KeriM

I have some code that looks for a header value and copies the column if a header value is found and pastes it on a new sheet. It's set up to copy non-contiguous data, so if it finds a blank cell in the data it does an xlUp to make sure it copies the entire data set.

Unfortunately, this means it still copies the column if the column is completely blank except for the header value. How can I prevent this from happening?

Code:

 For Each cell In headerRow
      Select Case cell.Value
       Case "Value 1", "Value 2", "Value 3"

          Set bottom = Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, _
                             cell.Column)
           If bottom.Value < "" Then
            Range(cell.Offset(1, 0).Address & ":" & bottom.Address).Copy
          Else
            Range(cell.Offset(1, 0).Address & ":" & Cells(bottom.End(xlUp).Row, _
                                             cell.Column).Address).Copy

So if Value 3 is blank except for the header row. It will paste "Value 3" instead of skipping that column. I trid to put a cells.count before that Else statement, to say if the cell count = 1 skip it, but it resulted in an overflow error. Any help is appreciated!

KeriM

Quote:

Originally Posted by KeriM

I have some code that looks for a header value and copies the column if a header value is found and pastes it on a new sheet. It's set up to copy non-contiguous data, so if it finds a blank cell in the data it does an xlUp to make sure it copies the entire data set.

Unfortunately, this means it still copies the column if the column is completely blank except for the header value. How can I prevent this from happening?

Code:

 For Each cell In headerRow
      Select Case cell.Value
       Case "Value 1", "Value 2", "Value 3"

          Set bottom = Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, _
                             cell.Column)
           If bottom.Value < "" Then
            Range(cell.Offset(1, 0).Address & ":" & bottom.Address).Copy
          Else
            Range(cell.Offset(1, 0).Address & ":" & Cells(bottom.End(xlUp).Row, _
                                             cell.Column).Address).Copy

So if Value 3 is blank except for the header row. It will paste "Value 3" instead of skipping that column. I trid to put a cells.count before that Else statement, to say if the cell count = 1 skip it, but it resulted in an overflow error. Any help is appreciated!

Nevermind, I figured it out. I needed to do a "Counta" before the else. Now it skips to the next column header.

NumberofCells = Application.CountA(cell)
If NumberofCells = 1 Then GoTo Skip

Claus Busch

Hi,

Am Mon, 8 Oct 2012 16:25:43 +0000 schrieb KeriM:

Unfortunately, this means it still copies the column if the column is
completely blank except for the header value. How can I prevent this
from happening?

try:
For Each rngC In headerrow
Select Case rngC.Value
Case "Value 1", "Value 2", "Value 3"
If WorksheetFunction.CountA(Columns(rngC.Column)) 1 Then
LRow = Cells(Rows.Count, rngC.Column).End(xlUp).Row
Range(Cells(1, rngC.Column), _
Cells(LRow, rngC.Column)).Copy

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus Busch

Hi,

or try:
For Each rngC In headerrow
Select Case rngC.Value
Case "Value 1", "Value 2", "Value 3"
LRow = Cells(Rows.Count, rngC.Column).End(xlUp).Row
If LRow 1 Then
Range(Cells(1, rngC.Column), _
Cells(LRow, rngC.Column)).Copy

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Count filled data rows until empty	Dallman Ross	Excel Discussion (Misc queries)	7	May 9th 23 11:43 AM
Hpw do I delete multiple empty rows found between filled rows?	Bill	Excel Worksheet Functions	2	November 15th 09 07:12 PM
Skip hidden rows while copying down in excel?	DRSTCSPM	Excel Discussion (Misc queries)	2	September 20th 06 07:32 PM
SKIP EMPTY ROWS FROM IMPORT	R.V. DEURSEN	Excel Programming	4	June 28th 04 07:17 PM
Skipping empty rows when copying a formula down a column	Rachel Jones	Excel Programming	1	July 31st 03 03:55 AM

Menu

About Us