why clear headers despite of start copying from row2?

[abdo meghari]

Hi guys,
I'm not sure why this problem occurs for me .
the code should clear from row2 for closed file "Bridgestone Stock Sales report(12)" before import data from open file , but will also clear headers for closed file !!

Sub OpenFilesFromFolder1()
  Dim ExtBk As Workbook
  Dim IntBk As Workbook
  Dim FolderPath As String
  Dim FilePath As String
  Dim lRow As Long
  Dim Rng1 As Range, Rng2 As Range
 
  Set IntBk = ActiveWorkbook

  lRow = IntBk.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

   FolderPath = "C:\Users\MY-NAME\DESKTOP\BRIDGESTONE REPORT\"
 
  FilePath = Dir(FolderPath & "Bridgestone Stock  Sales report(12).xls")
  If FilePath <> "" Then
    Set ExtBk = Workbooks.Open(FolderPath & FilePath)
  Else   
     MsgBox "File Bridgestone Stock  Sales report(2).xlsm not found"
     Exit Sub
  End If
  Application.ScreenUpdating = False

  With ExtBk.Worksheets("REPORT")
      .Range("A2:D" & .Cells(.Rows.Count, 1).End(xlUp).Row).ClearContents
  End With
  ExtBk.Worksheets("REPORT").Range("A2:A" & lRow).Value = IntBk.Worksheets("Sheet1").Range("A2:A" & lRow).Value
  ExtBk.Worksheets("REPORT").Range("D2:D" & lRow).Value = IntBk.Worksheets("Sheet1").Range("D2:D" & lRow).Value
  Set Rng1 = IntBk.Worksheets("Sheet1").Range("B2:C" & lRow)
  Set Rng2 = ExtBk.Worksheets("REPORT").Range("B2:C" & lRow)
  Rng2.Value = Rng1.Value
  Application.ScreenUpdating = True
  Application.DisplayAlerts = False
  ExtBk.Save
  ExtBk.Close
  Application.DisplayAlerts = True
End Sub

any help to fix this problem,please?

 

[abdo meghari]

so you mean my problem in this .Range("A2:D" because column D ?

 

[MARK858]

Only in the sense of the row number being produced by ExtBk.Worksheets("REPORT").Cells(Rows.Count, 1).End(xlUp).Row is returning a number lower than 2

 

[abdo meghari]

so I have two choices as you and Alex suggests or using loop by specify where start row from to avoid this problem?

 

[MARK858]

As Alex suggested or the code below which will prevent it accepting a 1 (will produce a 2 instead of a 1)

.Range("A2:D" & Application.Max(.Cells(.Rows.Count, 1).End(xlUp).Row, 2)).ClearContents

or base it on a column which always produces the required answer which obviously

ExtBk.Worksheets("REPORT").Cells(Rows.Count, 1).End(xlUp).Row

doesn't

 

[abdo meghari]

what about loop like this ?

for i=2 to lrow
      .Range("A2:D" & lrow).ClearContents

 

[MARK858]

what about loop like this ?
for i=2 to lrow

Loops are slow, and how would you define lrow?

 

[abdo meghari]

Loops are slow

bad choice
thanks for your time & consultation

 

[MARK858]

You're welcome