Using Excel PasteSpecial (Divide) on Row Count which varies

Question

I have a spreadsheet as follows:

Columns A:G (Name, Path, Size, Type, DateCreated, DateLastModified, DateLast Accessed)
Header uses rows 1:7
ColumnC contains file size in KB
Primary VBA populates from selected folder
Sub inserts 2 columns to left of ColumnD
Columns now: Name, Path, Size in KB, Size in MB, Size in GB, Type, DateCreated, DateLastModified, DateLast Accessed

I have a Sub to call which loops ColumnC to and inserts a divided amount into ColumnD & ColumnE

Looping works but takes time so would like to PasteSpecial where ColumnD = ColumnC/1000000 and ColumnE = ColumnC/1000000000

Each folder will have different quantities of files. To run the conversion Call I am using myRows = Cells(Rows.Count, 3).End(xlUp).Row - 7 (where 7 is the header-row deduction)"

I would like to PasteSpecial the myRows of ColumnC to ColumnD and ColumnE but, as noted myRows will be different for each folder. I am not finding (but still searching) where to put the divisor which will be used for the ColumnD and ColumnE calculation with:

PasteSpecial Operation:=xlPastSpecialOperationDivide

My Call code is:

Sub Convert_MB_GB()

    Dim myRows As Long
    Dim x As Long
    Dim var As Double
    Dim Num1 As Long
    Dim Num2 As Long
    Num1 = 1000000 'Divisor for KB to MB
    Num2 = 1000000000 'Divisor for KB to GB

        myRows = Cells(Rows.Count, 3).End(xlUp).Row - 7 'Count rows and subtract 7-row header

        Range("D:E").Insert 'Insert 2 columns to the left of Column D
        [D7].Value = "Size in MB" 'Add title to column
        [E7].Value = "Size in GB" 'Add title to column

        Range("C8").Select 'Starting Cell

        For x = 1 To myRows
            var = Application.ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
                ActiveCell.Value = var / Num1
            ActiveCell.Offset(0, 1).Select
                ActiveCell.Value = var / Num2
            ActiveCell.Offset(1, 0).Select
                ActiveCell.Offset(0, -2).Select
        Next

 Range("D:D").NumberFormat = "0.0" 'Configure Column D to 1 decimal point
 Range("E:E").NumberFormat = "0.0" 'Configure Column E to 1 decimal point

End Sub

No need to try to `PasteSpecial` here. Really would [advise against using `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also, you could either do the calculations with a formula, and then hard-code the values, or do the calcs in memory with a Variant array. — BigBen, Jan 21 '20 at 18:59

SJR · Answer 1 · 2020-01-21T19:06:25.087

Do you mean this?

Sub x()

Dim myRows As Long

myRows = Cells(Rows.Count, 3).End(xlUp).Row - 7

With Cells(1, 1000)
    .Value = 1000000 'some out of the way unused cell
    Range("D8").Resize(myRows).Value = Range("C8").Resize(myRows).Value
    .Copy
    Range("D8").Resize(myRows).PasteSpecial operation:=xlPasteSpecialOperationDivide
    .ClearContents
End With

End Sub

Or another approach

Sub x()

Dim myRows As Long

myRows = Cells(Rows.Count, 3).End(xlUp).Row - 7

With Range("D8").Resize(myRows)
    .Value = .Offset(, -1).Value
    .Value = Evaluate(.Address & "/1000000")
    'or in one line
    '.Value = Evaluate(.Offset(, -1).Address & "/1000000")
End With

End Sub

See here.

score 0 · Answer 2 · answered Jan 21 '20 at 19:00

You probably don't need to PasteSpecial and you should avoid using Select. Your loop can be simplified to:

    For x = 8 To myRows
        var = Cells(x,3)
        Cells(x,4) = var / Num1
        Cells(x,5) = var / Num2
    Next x

This will work much faster than selecting. You can also turn off ScreenUpdating and turn Calculation to manual while executing your code.

Using Excel PasteSpecial (Divide) on Row Count which varies

2 Answers2