Problem when using SpecialCells

[HeadshotSam]

Hi,

I have been attempting to use SpecialCells in VBA to extract all the numbers in a column (of formulas) and 'paste' in another sheet.

The problem I'm having is the SpecialCells argument is only reading in the first instance where there is a number.

Code:

Option Explicit

Sub SpecialCellsTest()

Dim countCells As Integer
Dim cellsToExamine As Range
Dim cellsToPaste As Range

Application.ScreenUpdating = False

countCells = Range("count_cells").Value

Set cellsToExamine = Range("examine_cells")
Set cellsToPaste = cellsToExamine.Specialcells(xlCellTypeFormulas, xlNumbers)

Sheets(3).Range("A3:A" & 2 + countCells) = cellsToPaste.value

Application.ScreenUpdating = True

End Sub

In this code I have count_cells as a count of the numbers in the range and examine_cells as a column of cells with a mixture of unique numbers and repeated text.

Example Range to carry out the code on could be

1
Apple
2
3
Apple
4
Apple
Apple
5
6
7
etc.

(I am running into this problem in Excel 2007 and Excel 2010 both running Windows 7)

Thanks,
HSS

 

[JoeMo]

You first sentence is not entirely clear. What was your simple test... omitting the xlNumbers or the statement that I posted in Message #4. It almost sounds like you are say that what I posted in Message #4 is copying the formulas for you.

Here's the setup for my simple test.

Excel Workbook
	A	B	C	D	E
1	6
2	21				1
3	31				1
4					1
5					1
6					1
7					1
Sheet2

Note that cells A2:A3 return text not numbers.
Using that setup, run these two macros:

Sub test1()
Columns("B").Clear
Range("A1:A3").SpecialCells(xlCellTypeFormulas, xlNumbers).Copy Range("B1")
End Sub
Sub test2()
Columns("B").Clear
Range("A1:A3").SpecialCells(xlCellTypeFormulas).Copy Range("B1")
End Sub

 

[Jonmo1]

The problem comes when the cells designated by specialcells are NON Contiguous

Now run the same two macros with the below setup

Excel Workbook
	A	B	C	D	E
1	6
2	21				1
3	8				1
4					1
5					1
6					1
7					1
Sheet1

 

[JoeMo]

The problem comes when the cells designated by specialcells are NON Contiguous

Very interesting, and for me unexpected, thanks for pointing that out Jonmo1.

 

[Rick Rothstein]

Okay, here is the apparent rule that I have been able to discern from all of this (and I am going to have to remember this)... if only a single contiguous range (one or more adjacent cells) is copied (no matter how it is formed, SpecialCells or otherwise), then the formulas (if any) get copied. If SpecialCells returns a non-contiguous range (apparently Copy won't work for other means of creating a non-contiguous range), then only the values are copied, not the formulas.

 

[Jonmo1]

I would agree with that.

Also, whatever the undrlying reason for this is, It also applies to

With Range.SpecialCells(..)
    .Value = .Value
End With

Doesn't work on Non Contiguous range.

 

[Rick Rothstein]

I would agree with that.

Also, whatever the undrlying reason for this is, It also applies to

With Range.SpecialCells(..)
    .Value = .Value
End With

Doesn't work on Non Contiguous range.

Interesting! Okay, so if you wanted to copy values from formulas along with formats, comments and whatnot, you could do something like this...

With Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlNumbers)
  .Copy Range("B1")
  If .Areas.Count = 1 Then .Value = .Value
End With

 

[Jonmo1]

Interesting! Okay, so if you wanted to copy values from formulas along with formats, comments and whatnot, you could do something like this...

With Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlNumbers)
  .Copy Range("B1")
  If .Areas.Count = 1 [COLOR="#FF0000"]Then .Value = .Value[/COLOR]
End With

That was almost pretty cool, but I think the highlighted part would be applied to the copied area, not the pasted area..

 

[Rick Rothstein]

That was almost pretty cool, but I think the highlighted part would be applied to the copied area, not the pasted area..

D@mn, you are right, I did that too fast. Starting to get cumbersome now, but this is what the code would have to look like (again, to copy values, formats, comments , etc)..

With Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlNumbers)
  If .Areas.Count = 1 Then
    Range("B1").Resize(.Count) = .Value
  Else
    .Copy Range("B1")
  End If
End With

 

[Jonmo1]

With Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlNumbers)
  If .Areas.Count = 1 Then
    [COLOR="#FF0000"]Range("B1").Resize(.Count) = .Value[/COLOR]
  Else
    .Copy Range("B1")
  End If
End With

Hate to keep doing this...but

Now that loses the formats, comments and whatnot..

 

[Jonmo1]

Is this what you're after

With Range("A1:A11").SpecialCells(xlCellTypeFormulas, xlNumbers)
    .Copy Range("B1")
    If .Areas.Count = 1 Then
        Range("B1").Resize(.Count).Value = Range("B1").Resize(.Count).Value
    End If
End With