How to work with data on multiple selection within a filter being applied?

Question

I had to rework the code a bit as I got the Error1004: pastespecial method of worksheet class failed I just specified the range for PasteSpecial xlPasteValues see the code:

Sub AvoidingSelect()

Dim rng As Range, cll As Range Set rng = Range("G2:G12854")

Sheets("Data").Range("G2:G12854").SpecialCells(xlCellTypeVisible).Copy Sheets("Data").Range("G2:G12854").PasteSpecial xlPasteValues

Application.CutCopyMode = False

For Each cll In rng

If IsNumeric(cll.Value) Then  Range("G2:G12854") = Application.WorksheetFunction.RoundDown(cll.Value, 8) End If

Next End Sub

However, the last part is not working. It picks the first cell.value in the row and copy it into each cell all the way down so the value of cell H1084 is in every cell now.

Can you elaborate on what the Issue is, how the data is and where you get an error in the code ? — Mikku, Jul 22 '19 at 14:49
Got stuck? What error are you getting? Why are you using `Selection`? Why not specify a range, and then work with that object? — Dean, Jul 22 '19 at 14:49
[Reading this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) may prove useful — cybernetic.nomad, Jul 22 '19 at 14:50
Well I actually used a specified range, though I am looking for some quicker script. — Samuel Balun, Jul 23 '19 at 10:43
@SamuelBalun please see my [edit](https://stackoverflow.com/a/57148860/11167163) — TourEiffel, Jul 23 '19 at 13:58

score 0 · Accepted Answer · edited Sep 26 '19 at 12:46

0

Reminder, What is Rounddown's syntax? From the Microsoft Excel documentation:

ROUNDDOWN(number, num_digits)

Number – Required. Any real number that you want rounded down.

Num_digits – Required. The number of digits to which you want to round number.

So in your code the first mistake I saw is here :

Selection.WorksheetFunction.RoundDown (Activecell.Value)

This line must be like below :

Application.WorksheetFunction.RoundDown (Activecell.Value , 0)

So your code would be like that :

Sub Macro1()

  Dim Activecell As Range
  Dim rounddownvar As Double

    Selection.SpecialCells(xlCellTypeVisible).Select

 For Each Activecell In Selection
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    rounddownvar = Application.WorksheetFunction.rounddown(Activecell.Value, 1)

    Debug.Print rounddownvar
 Next Activecell

End Sub

I added Application.WorksheetFunction.rounddown(Activecell.Value, 1) into a var because this line returns a number ...

Without select to be more efficient :

Sub AvoidingSelect()
Dim rng As Range, cll As Range
Set rng = Range(Range("A2:B2"), Range("A2:B2").End(xlDown)).SpecialCells(xlCellTypeVisible)

Sheets("Sheet1").Range("A1:AA100000").SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet1").PasteSpecial xlPasteValues

For Each cll In rng

If IsNumeric(cll.Value) Then
 Debug.Print Application.WorksheetFunction.RoundDown(cll.Value, 0)
End If

Next
End Sub

CAUTION:

Please respect RoundDown syntax :

RoundDown(cll.Value, 0) here 0 is the number of digit you want to delete after the , .

For Example :

RoundDown(cll.Value, 0) give : 0.000

RoundDown(cll.Value, 3) give : 0

Working :

Sub AvoidingSelect()
Dim rng As Range, cll As Range
Set rng = Range(Range("G2:G12854"), Range("G2:G12854").End(xlDown)).SpecialCells(xlCellTypeVisible)

Sheets("Sheet1").Range("G2:G12854").SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet1").Range("G2:G12854").PasteSpecial xlPasteValues

For Each cll In rng

If IsNumeric(cll.Value) Then
Debug.Print cll.Value
 cll = Application.WorksheetFunction.RoundDown(cll.Value, 8)
End If

Next
End Sub

edited Sep 26 '19 at 12:46

Martijn Pieters

1,048,767
296
4,058
3,343

answered Jul 22 '19 at 15:06

TourEiffel

4,034
2
16
45

Thank you for your answer! The code is not working because of the: _Run-Time error '1004': This action won't work on multiple selections._ – Samuel Balun Jul 23 '19 at 10:17
@SamuelBalun So you got multiple selection thats it ? – TourEiffel Jul 23 '19 at 12:32
@SamuelBalun please look at my Edit, you will have to fit `range` where your values are I fixed em as `A2:B2` . Waiting for your feedbacks ;) – TourEiffel Jul 23 '19 at 13:42
@SamuelBalun Try to copy and paste the last code avoiding select. Please **respect** `RoundDown` syntax : **RoundDown(cll.Value, 0)** here **0** is the number of digit you want to delete after the **,** . **For Example :** **RoundDown(cll.Value, 0)** give : 0.000 **RoundDown(cll.Value, 3)** give : 0 – TourEiffel Jul 23 '19 at 13:51
I had to rework the code a bit as I got the _Error1004: pastespecial method of worksheet class failed_ I just specified the range for _PasteSpecial xlPasteValues_ see the code: `Sub AvoidingSelect() Dim rng As Range, cll As Range Set rng = Range("G2:G12854") Sheets("Data").Range("G2:G12854").SpecialCells(xlCellTypeVisible).Copy Sheets("Data").Range("G2:G12854").PasteSpecial xlPasteValues Application.CutCopyMode = False For Each cll In rng If IsNumeric(cll.Value) Then Range("G2:G12854") = Application.WorksheetFunction.RoundDown(cll.Value, 8) End If Next End Sub` – Samuel Balun Jul 24 '19 at 13:12
@SamuelBalun See my last edit, I tested it its working you just got to copy and paste ;) – TourEiffel Jul 24 '19 at 13:48
@SamuelBalun Waiting for your feedbacks ;) – TourEiffel Jul 24 '19 at 13:49
Hi Dorian, thank you for your help. I managed to finish my code thanks to you! `Sub AvoidingSelect() Dim rng As Range, cll As Range Set rng = Range("G2:G12854") Sheets("Data").Range("G2:G12854").SpecialCells(xlCellTypeVisible).Copy Sheets("Data").Range("G2:G12854").PasteSpecial xlPasteValues Application.CutCopyMode = False For Each cll In rng If IsNumeric(cll.Value) Then cll = Application.WorksheetFunction.RoundDown(cll.Value, 8) End If Next End Sub ` – Samuel Balun Jul 25 '19 at 08:25
@SamuelBalun Glad that I could help you, may I ask you to accept answer ? – TourEiffel Jul 25 '19 at 08:26

How to work with data on multiple selection within a filter being applied?

1 Answers1