VBA - Fix auto fill issue when only 1 row

[Jeofbist3]

Hello all,

I have a bug issue on VBA when an auto fill formula can't be executed because only 1 row is available. I mean the next code can't be executed if I don't have at least 2 rows. 2 macros are impacted and here they are:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub RemoveDuplicates_Open()
Sheets("Sharepoint Extract").Activate
Columns("E:E").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$66666").RemoveDuplicates Columns:=1, Header:=xlNo
Dim lRow As Long
Range("B2").Select
ActiveCell.FormulaR1C1 = "=""'""&RC[-1]&""'""&"","""
lRow = ActiveSheet.UsedRange.Rows.Count
Range("B2").AutoFill Destination:=Range("B2:B" & lRow) // the issue is here with auto fill
Range(Range("B2"), Range("B2").End(xlDown)).Select
Application.CutCopyMode = False
Selection.NumberFormat = "@"
Selection.Copy
Sheets("Toad Query").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Sub GetQueries_Open()
Sheets("Sharepoint Extract").Activate
Dim lRow As Long
lRow = ActiveSheet.UsedRange.Rows.Count
Range("A2:C2").Select
Selection.AutoFill Destination:=Range("A2:C" & lRow) // same issue here with auto fill
Rows("1:1").Select
Selection.AutoFilter
Range("A1").AutoFilter Field:=1, Criteria1:="=0"
Range("B1").AutoFilter Field:=2, Criteria1:="OPEN"
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("D2:P" & LR).SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Paste for gA ASSA formula").Select
Range("A5").Select
ActiveSheet.Paste
Dim lngLastRow As Long
lngLastRow = Sheets("Paste for gA ASSA formula").Cells.Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row - 3
Sheets("Formula for gA ASSA").Select
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & lngLastRow)
ThisWorkbook.Sheets("Formula for gA ASSA").Copy
ActiveWorkbook.SaveAs "C:\Users" & Environ("Username") & "\Desktop\Open_Transfers" & Format(Date, "mmddyyyy") & ".xlsx", FileFormat:=51
End Sub</code>Please note that, the output of the code (at the end, creation and save the file) will be the same, no matter the number of rows I have (one or more) !

So my question is: what would be the adapted code if there's only one row (or more) in my data? For the 2 different macros. Thanks a lot in advance

 

[Fluff]

What do you want to do if there is only one row of data? Exit the macro, or continue without the autofill?

 

[Jeofbist3]

Hi Fluff,

I'd like to continue without the autofill and continue with the rest of the code. If exiting the macro, I won't get the file saved and won't be able to execute the rest..

thanks

 

[Fluff]

How about

lRow = ActiveSheet.UsedRange.Rows.Count
[COLOR=#0000ff]If lRow > 2 Then
   Range("B2").AutoFill Destination:=Range("B2:B" & lRow)
   Range(Range("B2"), Range("B2").End(xlDown)).Select
End If[/COLOR]
Application.CutCopyMode = False