Results 1 to 6 of 6

Is There A Special Way Of Formatting Lrow+1 Of Excel From Access?

  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    184

    Is There A Special Way Of Formatting Lrow+1 Of Excel From Access?

    I have a sub that exports my form's 4 subforms onto one worksheet in Excel. I'm now working on the formatting portion and I'm stuck on the row after last row.

    This is what I tried, but it tells me that .Rows is an invalid or unqualified reference.


    Code:
    Lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    Lcol = .Cells(lRow, .Columns.Count).End(xlToLeft).Column
    Set rng1 = .Range(.Cells(Lrow + 1, 1), .Cells(Lrow + 1, Lcol))
    I'm not finding any help in the VBA reference either. Does anyone know how to find the last row? In full transparency I want Lcol to be P:P because I have other subforms in columns to the right.

    I want to find the last row and format the row beneath it to be black with white font (.interior.ColorIndex = 1 etc..) But I don't know the range... range = .Range(.Cells(Lrow +1, 1), .Cells(Lrow +1, LCol))???


    UPDATE:
    I found how to find the last row +1: Lrow = wks.Cells(Rows.Count, "A").End(xlUp).Row + 1

    Now I need to figure out how to make the range, Lrow A:P

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,165
    have you looked at using usedrange to find the last row? generally much faster and reliable that using xlup if this is a new worksheet

    something like

    lRow=.usedrange.rows.count+1
    lcol=.usedrange.columns.count+1

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    184
    Thanks for the advice Ajax. Would you know how to call the range below Lrow? I've tried wks.Range("A:P" & Lrow) but I get this error: Click image for larger version. 

Name:	Untitled.png 
Views:	11 
Size:	13.9 KB 
ID:	34696
    How do I call that range so that I an format the row beneath the last row if Lrow = .usedrange.rows.count +1

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,165
    Think you need A1 rather than A or “A” & .used range.....

  5. #5
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    184
    That highlights everything from A1 to Lrow column P. That's the problem. the start cell (example A1) could be A30 or A40...

  6. #6
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    184
    I figured it out...

    wks.Range("A" & Lrow, "P" & Lrow).Select
    With Selection
    .......
    End With

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-19-2014, 10:26 AM
  2. Formatting Access Export to Excel
    By derekben in forum Access
    Replies: 1
    Last Post: 07-09-2013, 01:30 PM
  3. Formatting Access Export to Excel - VBA
    By derekben in forum Import/Export Data
    Replies: 2
    Last Post: 07-01-2013, 01:19 PM
  4. Formatting an Access table after importing from Excel
    By bigern87 in forum Import/Export Data
    Replies: 3
    Last Post: 02-13-2013, 06:08 AM
  5. Access VBA Excel Pivot Table Formatting
    By Tomlon in forum Programming
    Replies: 1
    Last Post: 02-07-2013, 01:28 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums