Vfrdtyky

So i wrote code that pastes a copied range from the clipboard into the visible cells only starting from the active cell the user marked

I already optimized my code somewhat speeding it up from 7200 Cells/min to 42000 cells/min but i think there still is plenty of room for optimization. But due to the fact that it is the third day programming VB for me i am asking the community for useful tips n tricks for making my code faster

The way i handle it is that i paste the clipboard into an new worksheet
and then search the next visible cell to insert into i use the variable max length to "limit" the search but in my application thousands of cells may be invisible.

I thought about using a StringBuilder to make it run faster but have no Idea how to implement that

important is that both rows and columns can be invisible

On Error GoTo ErrorHandler 'Enable Error Handling





Application.ScreenUpdating = False

Dim tblRow1 As Integer, lRow As Integer

Dim tblName As String

Dim lastRow, lastCol As Long

Dim outX, outY As Long

Dim maxLength As Long

clipboardTable As String     

outputTable As String  







outputTable = ActiveSheet.Name 'Safe the Name of the target sheet



outY = ActiveCell.Row           'Safe the Target position in sheet with xY coordinates

outX = ActiveCell.Column



 maxLength = Sheets(outputTable).UsedRange.Rows.Count



outYtmp = outY                  'Is needed to reset the corsur from the bottom to top



Set wbook = ActiveWorkbook

Set clipSheet = wbook.Sheets.Add





clipboardTable = clipSheet.Name



Sheets(clipboardTable).Activate

Sheets(clipboardTable).PasteSpecial





'Start Sheet

Sheets(clipboardTable).Select

lastRow = Sheets(clipboardTable).UsedRange.Rows.Count

lastCol = Sheets(clipboardTable).UsedRange.Columns.Count



'MsgBox ActiveSheet.UsedRange.Rows.Count

'MsgBox ActiveSheet.UsedRange.Columns.Count

'MsgBox " " & Sheets(inTable).Rows(1).EntireRow.Hidden







Sheets(outputTable).Select



For x = 1 To lastCol

Sheets(outputTable).Select

For j = 1 To maxLength

    If Sheets(outputTable).Columns(outX).Hidden = False Then

        For y = 1 To lastRow

            For i = 1 To maxLength

                If Sheets(outputTable).Rows(outY).Hidden = False Then



                    Sheets(outputTable).Cells([outY], [outX]) = Sheets(clipboardTable).Cells([y], [x])

                    outY = outY + 1

                    Exit For



                End If

            outY = outY + 1

            Next

        Next



        outX = outX + 1

        Exit For

    End If

 outX = outX + 1

Next

outY = outYtmp



Next

    Application.DisplayAlerts = False

    Sheets(clipboardTable).Delete

    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

Exit Sub



    ErrorHandler:  ' Error-handling routine.

    If (Worksheets(clipboardTable).Name <> "") Then

        Application.DisplayAlerts = False

        Sheets(clipboardTable).Delete

    Application.DisplayAlerts = True

End If





End Sub