Couple of macro questions

[chuckles1066]

I'm slowly getting my head around VBA and hopefully one day will be able to give something back to this great forum.

In the meantime, a couple of quickies:

(a) whats the code to copy or identify the complete range of data in a sheet?

(b) if I have a macro that assumes that another file is open, how do I generate an error message if the file isn't in fact open? And how do I stop the macro from stopping unceremoniously?

TIA.

 

[pboltonchina]

This will select the used range

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

 

[AlphaFrog]

This is one way to test if a workbook is open...

    Dim wb As Workbook
    
    On Error Resume Next                [COLOR="Green"]' Suspend error checking[/COLOR]
        Set wb = Workbooks("Test.xls")  [COLOR="Green"]' Name of open workbook to test for[/COLOR]
    On Error GoTo 0                     [COLOR="Green"]' Resume Error checking[/COLOR]
    
    If Not wb Is Nothing Then
        MsgBox "The workbook ""Test.xls"" is open."
    Else
        MsgBox "The workbook ""Test.xls"" is not open."
    End If

 

[chuckles1066]

This will select the used range

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

What if I want to simply identify that Sheet2 has data in cells A2:I42000?

Or cells A2:B3?

I just need a one-off bit of code that identifies last row and last column of data.

 

[chuckles1066]

This is one way to test if a workbook is open...

    Dim wb As Workbook
    
    On Error Resume Next                [COLOR="Green"]' Suspend error checking[/COLOR]
        Set wb = Workbooks("Test.xls")  [COLOR="Green"]' Name of open workbook to test for[/COLOR]
    On Error GoTo 0                     [COLOR="Green"]' Resume Error checking[/COLOR]
    
    If Not wb Is Nothing Then
        MsgBox "The workbook ""Test.xls"" is open."
    Else
        MsgBox "The workbook ""Test.xls"" is not open."
    End If

Will that work if the open file is a text file (albeit opened in Excel)?

 

[AlphaFrog]

Will that work if the open file is a text file (albeit opened in Excel)?

If the text file name you want to check if open is say Test.txt, then change in the code Test.xls to Test.txt

 

[AlphaFrog]

What if I want to simply identify that Sheet2 has data in cells A2:I42000?

Or cells A2:B3?

I just need a one-off bit of code that identifies last row and last column of data.

There are a couple of methods to determine the last cell on a sheet, in a row\column, within a range, etc. Each method has its own advantages, disadvantages, and limits. Here is a good site with example code on the various methods.
http://www.ozgrid.com/VBA/ExcelRanges.htm

Also, if you search this forum for something like last used cell, you will find several replies to this question.