Used Range problem 1

[jadams0173]

I'm struggling with getting the correct number of used rows. I've read thread707-1246047 but I can't get my arms around if. I'm very much a newbie when it comes to excel VBA. The problem is the used range for the rows doesn't include all the used rows after the insertion of some blank rows. I tried to 'reset' the value but to no avail. Also there is a lot of rem'd out code as I just found an example and started working with it trying to get it to where I needed to be.

Also if it isn't asking being overly 'greedy'. Why can't I do all my formating and adding text to the cells using the oSheet variable?

Private Sub FormatProductsWorksheet()
    Dim oSheet As Worksheet, oHdr As Range, nCols, nRows As Long
    Dim x As Integer
    
    Set oSheet = ThisWorkbook.Worksheets("Training")
    With oSheet
        nRows = .UsedRange.Rows.Count
        nCols = .UsedRange.Columns.Count
        Set oHdr = .Range(oSheet.Cells(1, 1), oSheet.Cells(1, nCols))

        oHdr.Font.Bold = True
        .Columns.AutoFit
                

    End With

     ThisWorkbook.Worksheets("Training").Activate

    'insert 4 blank rows
    ActiveSheet.Rows("1:6").Select
    Selection.Insert shift:=xlDown
    Cells(1, 1).Select
    
 [red]    'RESET THE BVALUE TO THE NUMBER OF USED ROWS
    nRows = ThisWorkbook.Worksheets("TRAINING").UsedRange.Rows.Count
   Debug.Print nRows
  [/red] 
'Report Title
    ActiveSheet.Cells(1, 3) = "Employee Gap Analysis by Job"
    With ActiveSheet.Range("a1", "d1")
        .Merge
        .Font.Bold = True
        .Font.Size = 14
        .HorizontalAlignment = xlCenter

    End With
'Employee number comes from X8.  Inserted into sheet by vb app
    ActiveSheet.Cells(2, 3) = "Employee Number: " & Cells(8, 24)
    With ActiveSheet.Range("a2", "d2")
        .Merge
        .Font.Bold = True
        .Font.Size = 14
        .HorizontalAlignment = xlCenter
    End With
    
'Employee name comes from First name (V8) Last name (W8)
    ActiveSheet.Cells(3, 3) = "Employee Name: " & Cells(8, 22) & " " & Cells(8, 23)
    With ActiveSheet.Range("a3", "d3")
        .Merge
        .Font.Bold = True
        .Font.Size = 10
        .HorizontalAlignment = xlCenter
    End With
    
'Required Courses label and job number
    ActiveSheet.Cells(5, 3) = "Required Courses for Job: " & Cells(8, 25)
    With ActiveSheet.Range("a5", "d5")
        .Merge
        .Font.Bold = 10
        .HorizontalAlignment = xlCenter
    End With
    
[red]
'APPLY CONDITIONAL FORMATING TO MAKE EXPIRED DAETS RED IF THEY ARE LESS THAN OR = TODAY'S DATE
    Cells(8, 4).Select
  With ThisWorkbook.Worksheets("TRAINING").Range("d8", "d" & nRows).Select
'      Columns("D:D").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="=today()"
    Selection.FormatConditions(1).Font.ColorIndex = 3
  End With
  [/red]
 'Centers the data in the entire sheet
'    With ActiveSheet.Range(ActiveSheet.Cells(7, 1), ActiveSheet.Cells(nRows, nCols))
'        .HorizontalAlignment = xlCenter
'    End With
   
    Cells(1, 1).Select
End Sub

I tried to have patience but it took to long! -DW

 

[Zathras]

UsedRange can be difficult to work with. You have to understand that it means exactly what it says. Inserting blank rows at the top or blank columns to the left does not change the number of rows in the UsedRange, it merely moves the UsedRange down that number of rows or right that number of columns.

Sub demo()
  With ActiveSheet
    sBefore = "Before: " & .UsedRange.Address & "  Rows: " & .UsedRange.Rows.Count
    Rows("1:5").Select
    Selection.Insert Shift:=xlDown
    sAfter = "After:   " & .UsedRange.Address & "  Rows: " & .UsedRange.Rows.Count
  End With
  MsgBox sBefore & vbNewLine & sAfter
End Sub

Note that UsedRange includes cells that may be empty but have specific formatting. (That's the tricky part.)

 

[jadams0173]

Thanks Zathras for the reply. The explanation and demo you provided were great. I know now to take used range literally and the demo show perfectly how the address of the cells changed but the used rows remained the same.

In the thread in my OP there is discussion on how to count the used ways with various methods. Can you expand on one of there methods or share an example of you own? Please.

I tried to have patience but it took to long! -DW

 

[Zathras]

It really depends on your particular worksheet.

In your case, simply adding 6 to nRows might be all you need.

Alternatively, you could wait until after you have entered the title on row 1 and then the .UsedRange.Rows.Count would include the inserted rows.

I generally work with lists, so I usually employ the xlEndDown method. But watch out for the case where there is only one row in the column.

 

[firefytr]

The UsedRange is unreliable. Check out this FAQ...

http://www.tek-tips.com/faqs.cfm?fid=2112

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci