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?

 

[Alex Blakenburg]

I am not exactly sure which workbook / worksheet you are referring but in case it helps

The next line will return a row number of 1 if the only thing in column A is the heading and it is in row 1

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

Also the next line with produce an address of A2:D1 if the only thing in column A is a heading in Row 1

 With ExtBk.Worksheets("REPORT")
      .Range("A2:D" & .Cells(.Rows.Count, 1).End(xlUp).Row).ClearContents
  End With

 

[abdo meghari]

Hi Alex
I'm talking about the file contains REPORT sheet as you indicate to second part .
as you see the line starts from A2, so why clear the headers A1:D1 ?!

 

[Alex Blakenburg]

I take it you figured it out ie a range of A2:D1 will clear row 1.

 

[abdo meghari]

ie a range of A2:D1 will clear row 1

how ??!
this fixes my problem (keep the headers ), doesn't clear row1. that's why I ask the original code to understand the main reason to clear header despite of indicates A2.

 

[Alex Blakenburg]

You could change the code to something like this.
(it will cater for there being no data in Column A under the heading row)

Sub test3()

    Dim lRowRpt As Long
    
    With ExtBk.Worksheets("REPORT")
        lRowRpt = .Range("A" & Rows.Count).End(xlUp).Row
        If lRowRpt <> 1 Then .Range("A2:D" & lRowRpt).ClearContents
    End With

End Sub

 

[abdo meghari]

You could change the code to something like this.

This is more logical and understandable for me .
thanks very much .

 

[MARK858]

as you see the line starts from A2, so why clear the headers A1:D1 ?!

Perhaps the below makes it clearer
A range must always be a rectangle starting at the lowest row number and the lowest column number/letter, to the highest row number and the highest column number/letter...
so if there is only 1 row in column A the red part below will evaluate to 1

 With ExtBk.Worksheets("REPORT")
      .Range("A2:D" & .Cells(.Rows.Count, 1).End(xlUp).Row).ClearContents
 End With

so your rectangle will be

 .Range("A2:D1")

which if you wrote it normally would be

 .Range("A1:D2")

as A is the lowest column letter and 1 is the lowest row number to D the highest column letter and 2 the highest row number

 

[abdo meghari]

thanks Mark for your sharing to this information .
What I face difficultly to understand 1 supposes this indicates to first column
so I understand The determinant for where start row number is from the range .Range("A2:D" , not 1.

 

[MARK858]

The first cell in the range is determined by the lowest row number and the lowest column in the range wherever they appear in the range (the first part of the range or the second), you can't have a range that isn't a rectangle.

If your last cell in Column A of sheet ExtBk.Worksheets("REPORT") is either in row 1 or there are no used cells it will produce a 1

If you had a range B23:H11 for example then the actual range would be B11:H23