CODEWITHSUNDEEP
excelvba
David.L
David.L

Reputation: 117

Excel VBA - Paste to visible cells

I have a template that's linking to external source.

My predecessor created it and that for 'easiness' on the eye, he/she created it by skipping a row. i.e. row 1 then row 3, row 5, row 9, row 13 etc HAS FORMULA, whereas in between those mentioned rows are just DEFAULT EMPTY CELL.

I've created a vba that opens the workbook and copy the sheet that I want.

If I were to use the code below, it's running very slowly, and for some reason, it loops more than once.

for each cell in usedrange
if cell.hasformula = true and instr(cell.formula, "SUMIF") > 0 then
      cell.formulaR1C1 = "='\\tel\folder1\folder2\[xlsheet.xlsx]SheetName'!RC
   end if
next cell

Therefore, what I've done is to actually assign it once, copy it and then paste to the respective cells (as shown below).

Workbooks(desWB).Sheets(maxSheet + 1).Range("J5").FormulaR1C1 = fullPath
Workbooks(desWB).Sheets(maxSheet + 1).Range("J5").Copy
Workbooks(desWB).Sheets(maxSheet + 1).Range("J6:J12,E48:J55,E57:J58,E61:J79,E84:J93,E96:J96,E99:J103").PasteSpecial Paste:=xlPasteFormulas

The latter method works and it's definitely much faster than the first. However, now I am facing a situation where due to the setup of the template, some rows have formulas and some doesn't, and it goes to thousands of rows. The skipping of rows too sometimes is not an increment of 2, it could be 3, 5 etc.

So am wondering if there's a way that's more effective and efficient to:

Upvotes: 0

Views: 2391

Answers (2)

RaphaelDantas
RaphaelDantas

Reputation: 1

Code line to execute:

Range("A1:A10").SpecialCells(xlCellTypeVisible).Value = "1"

'This line send 1 to Visible Cells in A1:A10 range

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

If you only want to process rows where the first cell in that row has a non-empty cell value then you should iterate the Ranges rows and columns and skip the rows when the first cell fails the test.

Your current code that uses a For Each cell in range approach will still keep processing cells in an empty row - which is redundant.

You can use code like below to skip the blank rows and only apply conditional logic to rows where you are confident that some cells have the formula you want to update. In the example, I use Range("C4:E10") but you can substitute for the Range that works for you depending on your workbook structure.

Option Explicit

Sub Test()
    'could pass in UsedRange of the sheet...
    IterateRange ThisWorkbook.Worksheets("Sheet1").Range("C4:E10")
End Sub

Sub IterateRange(rng As Range)

    Dim rngCell As Range
    Dim intX As Integer
    Dim intY As Integer

    'iterate all cells in range
    For intX = 1 To rng.Rows.Count
        For intY = 1 To rng.Columns.Count
            'get a cell
            Set rngCell = rng.Cells(intX, intY)
            'check if cell is blank or empty
            If IsEmpty(rngCell.Value) Or rngCell.Value = "" Then
                'skip the rest of the columns in this row and goto next row
                Exit For
            Else
                'this row has non-empty cells - do something
                Debug.Print rngCell.Address
                'some other test
                If rngCell.HasFormula And InStr(1, rngCell.Formula, "SUMIF") Then
                    'update formula...
                    Debug.Print rngCell.Formula
                End If
            End If
        Next intY
    Next intX

End Sub

Upvotes: 1

Related Questions