Help - VBA If no data in column stop code and display message (Code included)

[LNG2013]

Hello,

I have the following start code to my document.
I am trying to edit it so that if the sheet Data has no data in A2 or below it stops the SUB and displays a message saying there is no data based on the user selection, please change parameters and try again.

I tried with IF and ELSE but it is giving errors. Any help is appreciated!

Sub AStart()


Application.ScreenUpdating = False ' Added to speed up the process
Sheets("Data").Cells.Clear ' Clear Data WS in case data was accidentally left over.

    Workbooks.Open Filename:="C:\temp\ReportOutput.xls" ' Opens Access exported Excel file
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy ' Select and copy all the data from ReportOutput.xls
    Windows("Report.xlsm").Activate
    Sheets("Data").Select
    Range("A1").Select
    ActiveSheet.Paste    ' Activate and Paste data back over to
    Application.CutCopyMode = False
    'Sheets.Add Before:=ActiveSheet
    
    
[B] ' This code is being placed so if there is no data to exit any further code and let user no there is no data between that range.
 
If Data.Range("A2").Value = "" Then Exit Sub[/B]


    Sheets("PivotTable").Select
    Application.DisplayAlerts = True
    Set PSheet = Worksheets("PivotTable")
    Set DSheet = Worksheets("Data")
    'Define Data Range
    lastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(lastRow, LastCol)
    'Define Pivot Cache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(5, 1), _
    TableName:="PivotTable2")


Call AMasterBuild2

End Sub

 

[HaHoBe]

Hi LNG2013,

what about

Sub MrE1219954()
'https://www.mrexcel.com/board/threads/help-vba-if-no-data-in-column-stop-code-and-display-message-code-included.1219954/
With ActiveSheet
  If .UsedRange.Cells.Count = 1 And .Range("A1").Value = "" Then Exit Sub
End With
MsgBox "Continuing..."
End Sub

You should start working on the code to get rid of all the Selections...

Ciao,
Holger

 

[LNG2013]

Hi LNG2013,

what about

Sub MrE1219954()
'https://www.mrexcel.com/board/threads/help-vba-if-no-data-in-column-stop-code-and-display-message-code-included.1219954/
With ActiveSheet
  If .UsedRange.Cells.Count = 1 And .Range("A1").Value = "" Then Exit Sub
End With
MsgBox "Continuing..."
End Sub

You should start working on the code to get rid of all the Selections...

Ciao,
Holger

Thank you