Setting Dynamic Range (1 Viewer)

[msk7777]

I have the below code (borrowed) that reformats a query that was exported to excel. I need to define a range that would be all the data on the spreadsheet. Each output of this query changes so it needs to find the range. Can anyone help me adjust my Set of "myrange1" since all the definitions I have tried is not working?

Public Sub ModifyExportedExcelFileFormats(sFile As String)
Dim xlApp As Object
Dim xlSheet As Object
Dim myrange1 As Object
Set xlApp = GetObject(, "Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
Set myrange1 = xlSheet.Range("A1", xlSheet.Range("A" & Rows.Count).End(xlUp))
 
With xlApp
.Application.Sheets("C500 Provider Directory Query").Activate
    .Columns("A:J").Select
    .Selection.EntireColumn.AutoFit
    .Columns("A:J").Interior.ColorIndex = xlNone
    .Columns("A:J").Borders.LineStyle = xlLineStyleNone
    .Range("A1:J1").Interior.Color = 12611584
    .Range("A1:J1").Font.Color = vbWhite
    myrange1.Borders(xlDiagonalDown).LineStyle = xlNone
    myrange1.Borders(xlDiagonalUp).LineStyle = xlNone
    With myrange1.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myrange1.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myrange1.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myrange1.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myrange1.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myrange1.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    .Call AlternatRowColors
    .Sheets("C500 Provider Directory Query").Name = "500 Series Provider Network"
    .ActiveWorkbook.Save
End With
End Sub

 

[Ranman256]

the range is set in access, in a form, with 2 txt boxes, txtStartDate, txtEndDate
the query uses these boxes as its range.
THEN its exported to excel, via transferspreadsheet.

 

[msk7777]

Thanks for replying, however, I'm afraid that isn't what I am asking here. This request involves a query that has already been exported to Excel. I am attempting to format that spreadsheet after the export is complete.

the range is set in access, in a form, with 2 txt boxes, txtStartDate, txtEndDate
the query uses these boxes as its range.
THEN its exported to excel, via transferspreadsheet.

 

[The_Doc_Man]

This looks suspiciously like the problem posed by Squid1622. It seems to be a class assignment from its appearance. We try to avoid doing class assignments since we don't get graded for them.

 

[msk7777]

I will do my best to not get further agitated and will politely state that I am 39 years old and have been out of school for a very long time. I simply posted my question to get help on my work database.

If you reviewed any of my previous posts you wouldn't have just considered me a waste of your time.

Also, I just looked at Squid's post and it is not the same question. I don't know how to set the range to select all the data on the worksheet, I know how to select a specific range but not so that it selects it all. Every variation I have tried doesn't work. I wouldn't consider myself a VBA expert which is why I come here for help.

This looks suspiciously like the problem posed by Squid1622. It seems to be a class assignment from its appearance. We try to avoid doing class assignments since we don't get graded for them.

 

[Ranman256]

That's the cart before the horse.

 

[Squid1622]

I will do my best to not get further agitated and will politely state that I am 39 years old and have been out of school for a very long time. I simply posted my question to get help on my work database.

If you reviewed any of my previous posts you wouldn't have just considered me a waste of your time.

Also, I just looked at Squid's post and it is not the same question. I don't know how to set the range to select all the data on the worksheet, I know how to select a specific range but not so that it selects it all. Every variation I have tried doesn't work. I wouldn't consider myself a VBA expert which is why I come here for help.


Quote:
Originally Posted by The_Doc_Man
This looks suspiciously like the problem posed by Squid1622. It seems to be a class assignment from its appearance. We try to avoid doing class assignments since we don't get graded for them.

I can confirm that MSK and I are not working on a class assignment and each of us need help in our own right. We're not even in the same state and both are working professionals. Do not judge harshly and please help if you can.

 

[namliam]

Squid does seem to have a working selection method though...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lastRow As Long
Dim Range1 As Range
lastRow = Sheet1.Range("B77").End(xlUp).Row
'Let's assume your table begins at cell A3 and has two columns
Set Range1 = Sheet1.Range("B27").Resize(lastRow, 9)
Range1.Name = "WorkRequest"
Set Range1 = Nothing
End Sub

 

[msk7777]

namliam - thanks for leading me to review Squid's post further. Let me start by saying that I am self-taught in regards to VBA and may be overlooking something so please forgive me in advance.

I previously used this code in another database where the columns and rows were static. Now I am trying to adapt it for a query where the amount of rows are never static.

In reference to what you stated to Squid, I would rather you teach me how to fish on this. How I interpreted Squid's code was that his "lastrow" was static at B77 and then he was selecting up to the top row. I am needing to know how to set "myrange1" to find the bottom row and select up. I will add that the columns do remain static ("A:J").

Once I can make this adaptation I can handle the rest of the alterations I need to make to the code.

Thanks in advance for any insight you can offer!

Squid does seem to have a working selection method though...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lastRow As Long
Dim Range1 As Range
lastRow = Sheet1.Range("B77").End(xlUp).Row
'Let's assume your table begins at cell A3 and has two columns
Set Range1 = Sheet1.Range("B27").Resize(lastRow, 9)
Range1.Name = "WorkRequest"
Set Range1 = Nothing
End Sub

 

[Squid1622]

namliam - thanks for leading me to review Squid's post further. Let me start by saying that I am self-taught in regards to VBA and may be overlooking something so please forgive me in advance.

I previously used this code in another database where the columns and rows were static. Now I am trying to adapt it for a query where the amount of rows are never static.

In reference to what you stated to Squid, I would rather you teach me how to fish on this. How I interpreted Squid's code was that his "lastrow" was static at B77 and then he was selecting up to the top row. I am needing to know how to set "myrange1" to find the bottom row and select up. I will add that the columns do remain static ("A:J").

Once I can make this adaptation I can handle the rest of the alterations I need to make to the code.

Thanks in advance for any insight you can offer!

msk7777,

I found that code on another website, unfortunately I've misplaced the address. When I found the code though, the original designer set the end range as "A65565". Essentially (at least as I interpret it) the code counts from end of your "static" range up to the last row you have text and then redfines the named range as being only between your starting point and your last row.

My issue was that I started my range 27 rows down and didn't realize that in order to get to only the last row, I would have to subtract the first 26 rows from the defined range. If it's possible, it may be easier to just designate a last row on the sheet to start at that you know someone will never get to.

For my need, I may only get 100 records per sheet at most. So setting it in the 10,000 - 100,000 cell range will work fine. If you max out at 20,000 records, the I would start it somewhere closer to the 100,000 cell range mark.

 

[sxschech]

Didn't read your post too thoroughly, but if you are looking for determining last row, this is what I have been using in my access vba to format an excel file after exporting a query.

 'http://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba
            lastrow = .Range("A1").currentregion.rows.Count
            lastCol = .Range("A1").currentregion.Columns.Count

Another thing I've done for certain other cases when working with a recordset, I use the count from that and define that as the last row.

lastrow = rs.RecordCount

 

[sneuberg]

Concerning

lastrow = rs.RecordCount

Suggest

rs.MoveLast
lastrow = rs.RecordCount

To make sure the count is right.

 

[The_Doc_Man]

OK, msk7777, I guess I owe you an apology - but you would not believe how often we get a student assignment that suddenly floods us with ten versions of the same question.

In any case, it appears that you are getting help from the others so I'll step away from the curb.

 

[sxschech]

Sorry should have copied a little more of my code regarding the record count. Thanks for catching that.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim NumOfRecords As Long

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Your_tablename", DB_OPEN_DYNASET)

...
...
    rs.MoveLast
    NumOfRecords = rs.RecordCount
    rs.MoveFirst
...
...

 

[msk7777]

Thank you so much for your help everyone! Sorry for the delay in response but I have been trying all your suggestions, however, I am not getting any to work.

sxschech - I'm a tad bit confused as to why I am referring to my database table when I'm trying to count the rows of the spreadsheet? Please excuse me if that is a dumb question.

Also, I tried your first suggestions and keep getting an error pointing to the lastrow line. Says its an Object Variable error. Do you see where the issue is?

And to clarify - this code gets called from another outputto sub.

Public Sub ModifyExportedExcelFileFormats(sFile As String)
Dim xlApp As Object
Dim xlSheet As Object
Dim myrange1 As Object
Dim lastRow As Long
Dim lastCol As Long
lastRow = xlSheet.Range("A1").CurrentRegion.Rows.Count
lastCol = xl.Sheet.Range("A1").CurrentRegion.Columns.Count
Set xlApp = GetObject(, "Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
Set myrange1 = xlSheet.Range(lastCol & lastRow)

Sorry should have copied a little more of my code regarding the record count. Thanks for catching that.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim NumOfRecords As Long

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Your_tablename", DB_OPEN_DYNASET)

...
...
    rs.MoveLast
    NumOfRecords = rs.RecordCount
    rs.MoveFirst
...
...

 

[sxschech]

Sorry for confusion. Was just giving different ways to try and define lastrow.

As for your code, the lastrow needs to go after the sheet has been opened.

Public Sub ModifyExportedExcelFileFormats(sFile As String) 
Dim xlApp As Object 
Dim xlSheet As Object 
Dim myrange1 As Object 
Dim lastRow As Long 
Dim lastCol As Long 

Set xlApp = GetObject(, "Excel.Application") 
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1) [COLOR=Red]

lastRow = xlSheet.Range("A1").CurrentRegion.Rows.Count 
lastCol = xl.Sheet.Range("A1").CurrentRegion.Columns.Count[/COLOR]

Set myrange1 = xlSheet.Range(lastCol & lastRow)

 

[msk7777]

Thank you so much! That was the issue. I was able to get the code correct!

Public Sub ModifyExportedExcelFileFormats(sFile As String)
Dim xlApp As Object
Dim xlSheet As Object
Dim myrange1 As Object
Dim myrange2 As Object
Dim myrange3 As Object
Dim lastRow As Long
Dim Cell As Range
 Set xlApp = GetObject(, "Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
 lastRow = xlSheet.Range("A1").CurrentRegion.Rows.Count
 Set myrange1 = xlSheet.Range("A1:J" & lastRow)
Set myrange2 = xlSheet.Range("A2:J" & lastRow)
Set myrange3 = xlSheet.Range("A3:J" & lastRow)

Sorry for confusion. Was just giving different ways to try and define lastrow.

As for your code, the lastrow needs to go after the sheet has been opened.

Public Sub ModifyExportedExcelFileFormats(sFile As String) 
Dim xlApp As Object 
Dim xlSheet As Object 
Dim myrange1 As Object 
Dim lastRow As Long 
Dim lastCol As Long 

Set xlApp = GetObject(, "Excel.Application") 
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1) [COLOR=red]

lastRow = xlSheet.Range("A1").CurrentRegion.Rows.Count 
lastCol = xl.Sheet.Range("A1").CurrentRegion.Columns.Count[/COLOR]

Set myrange1 = xlSheet.Range(lastCol & lastRow)

 

[sxschech]

Glad you got it working.