Can I nest an 'If' statement in a 'With' statement?

[DK]

Using Excel 2007.
Can I nest an If statement in a With statement?
If not, how can I rearrange the code below to carry out the code only on
Sheet1?
Whenever I try to place an If statement inside the With statement I get the
Compile error: End With without With.
Any advice is appreciated.

Sub Clear_Entries()
Dim fr As Integer, lr As Integer, fc As Integer, lc As Integer
fr = Range("MtrHeader").Row + 1
lr = Sheet1.Range("A65536").End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
With Sheet1
.Range("com_entries").ClearContents
If lr >= fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
End With
End Sub

 

[Rick Rothstein]

For what you posted, you are missing the End If statement for your
If..Then..Else statements.

With Sheet1
.Range("com_entries").ClearContents
If lr >= fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
' You Else condition statements would go here, if any
End If
End With

 

[Peter T]

Sub Clear_Entries()
Dim fr As Long, lr As Long, fc As Long, lc As Long
Dim nRows As Long

fr = Range("MtrHeader").Row + 1
With Sheet1
nRows = .Rows.Count
fr = Range("MtrHeader").Row + 1
lr = .Range("A" & nRows).End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
.Range("com_entries").ClearContents
If lr >= fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else

End If
End With
End Sub

Note change of Integer to As Long and nRows for 65536 (ie for xl2007 and
earlier). I didn't test it though.

Regards,
Peter T

 

[DK]

So elementary :-|
Thanks Rick
DK

For what you posted, you are missing the End If statement for your
If..Then..Else statements.

With Sheet1
.Range("com_entries").ClearContents
If lr >= fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else
' You Else condition statements would go here, if any
End If
End With

 

[Per Jessen]

Hi

You miss an End If statement before End With.

With Sheet1
.Range("com_entries").ClearContents
If lr >= fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
End If
End With

Regards,
Per

 

[DK]

Thank you Peter for your prompt reply.
DK

Sub Clear_Entries()
Dim fr As Long, lr As Long, fc As Long, lc As Long
Dim nRows As Long

fr = Range("MtrHeader").Row + 1
With Sheet1
nRows = .Rows.Count
fr = Range("MtrHeader").Row + 1
lr = .Range("A" & nRows).End(xlUp).Row
fc = Range("MtrHeader").Column
lc = Range("MtrHeader").Columns.Count
.Range("com_entries").ClearContents
If lr >= fr Then
.Range(.Cells(fr, fc), .Cells(lr, lc)).ClearContents
Else

End If
End With
End Sub

Note change of Integer to As Long and nRows for 65536 (ie for xl2007 and
earlier). I didn't test it though.

Regards,
Peter T

 

[DK]

Thank you for the prompt reply. Much appreciated.
DK