How to bring in data from api without it clearing all the contents of my worksheet?

[jreid1]

So i have some vba code which allows me to import data form a website using an api. This data take up the columns A to K and approx 50000 rows each. The problem is that whenever i run this code to bring in my data it clears all the contents on the worksheet before the data was there. In the L columns and a couple afterwards I want to apply formulas to the data however I am currently unable to do this due to these formulas being cleared out whenever I bring the data in. Is there anyway to bring in this data without clearing out all the rows and columns of the spreadsheet and instead just A1 to K50000? Here's my code

Sub UpdateAllData()
    Const NUM_COLS As Long = 11 'how many columns we're copying
    Dim strEnappsysPriceForecastAPI As String, wb As Workbook
    Dim data() As Variant, lr As Long, wsData As Worksheet
    
    GoFast
    
    Set wb = Workbooks.Open(Range("EnappsysPriceForecast_API").Value)
    Set wsData = wb.Worksheets(1)
    lr = LastUsedRow(wsData)    'last row of data
    If lr > 0 Then              'have data?
        data = wsData.Range("A1").Resize(lr, NUM_COLS).Value
      
    End If 'have any data
    
    
    wb.Close

        With Range("EnappsysPriceForecastImport")
          .ClearContents
          .Cells(1).Resize(UBound(data, 1), NUM_COLS).Value = data
        End With
    
    GoFast False
    
    ThisWorkbook.Worksheets("CONFIG").Select
    MsgBox "Data has been updated"

End Sub

'maximize code speed by turning off unneeded stuff
'******** must reset !!!!
Sub GoFast(Optional bYesNo As Boolean = True)
  With Application
      .ScreenUpdating = Not bYesNo
      .Calculation = IIf(bYesNo, xlCalculationManual, xlCalculationAutomatic)
  End With
End Sub

'Return the last used row on a worksheet (or zero if the sheet is empty)
Function LastUsedRow(ws As Worksheet) As Long
    Dim f As Range
    Set f = ws.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, _
                    LookIn:=xlFormulas, SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, MatchCase:=False)
    If Not f Is Nothing Then LastUsedRow = f.Row 'else zero
End Function

Any help is greatly appreciated thank you.

 

[jreid1]

Found the answer

  LastRow = Range("L" & Rows.Count).End(xlUp).Row
        With Range("EnappsysPriceForecastImport")
        .Range("A1:K50000").ClearContents
        .Cells(1).Resize(UBound(data, 1), 11).Value = data

 

[Johnny C]

You may find these usefiul. Various ways of finding the last cell, Different circumstances need different methods.

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
  LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

'Using SpecialCells Function
  LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).row

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).row

'Using UsedRange
'  sht.UsedRange 'Refresh UsedRange
  LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).row

'Using Table Range
  LastRow = sht.ListObjects("SuppTbl").Range.Rows.Count

'Using Named Range
  LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
  LastRow = sht.Range("A1").CurrentRegion.Rows.Count