• Hi,


    i have the following code which works well, except that it wipes out the gridlines. The only gridlines it does keep are the ones around the cells containing information. Any one any ideas how i can place grid lines around all the rows or for the first 40 rows?


    Sub CommandButton3_Click()
    Dim d1 As Date, d2 As Date, d As Date, i As Long, n As Long, j As Integer, k As Long, t As Worksheet
    d1 = Range("f2")
    d2 = Range("h2")
    Set t = Worksheets("DUE")
    t.UsedRange.Offset(1, 0).Clear
    n = Range("P65536").End(xlUp).Row
    k = 1
    For i = 7 To n
    For j = 9 To 16
    d = Cells(i, j)
    If d >= d1 And d <= d2 Then
    k = k + 1
    Range("A" & i & ":D" & i).Copy t.Cells(k, 1)
    With t.Cells(k, 7)
    .Value = d
    .NumberFormat = Cells(i, j).NumberFormat
    End With
    Exit For
    End If
    Next j
    Next i
    t.Activate


    End Sub



    Regards


    Bob

  • Gridlines


    Hi,


    i have the following code which works well, except that it wipes out the gridlines. The only gridlines it does keep are the ones around the cells containing information. Any one any ideas how i can place grid lines around all the rows or for the first 40 rows?
    Sub CommandButton3_Click()
    Dim d1 As Date, d2 As Date, d As Date, i As Long, n As Long, j As Integer, k As Long, t As Worksheet
    d1 = Range("f2")
    d2 = Range("h2")
    Set t = Worksheets("DUE")
    t.UsedRange.Offset(1, 0).Clear
    n = Range("P65536").End(xlUp).Row
    k = 1
    For i = 7 To n
    For j = 9 To 16
    d = Cells(i, j)
    If d >= d1 And d <= d2 Then
    k = k + 1
    Range("A" & i & ":D" & i).Copy t.Cells(k, 1)
    With t.Cells(k, 7)
    .Value = d
    .NumberFormat = Cells(i, j).NumberFormat
    End With
    Exit For
    End If
    Next j
    Next i
    t.Activate


    End Sub



    Regards


    Bob

  • Hi Bob,


    Do the cells you are copying from in the statement:


    Range("A" & i & "D:" & i).Copy t.Cells(k, 1)


    have any fill colour formatting, e.g. white instead of 'no fill'? If so, try using Copy & Paste Special / Values.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • not sorting all data


    Richie,


    sorry didnt realise i posted it twice till after i done it. i have only just noticed that it does not select dates after coloumn p, i have tried changing the:
    n = range ("p65536") but it made no difference. my dates could in theory go right upto "IV". Do you think i have mucked up someting by trying to put the gridlines back on.


    Regards


    Bob

  • Hi Bob,


    First things first - did either of the suggestions help with the grid-lines problem?


    As regards the columns query, you need to make sure you have grasped the way that the Cells method operates. Its used in the R1C1 style, that is the first argument is the row and the second is the column. So, Cells(1,2) refers to cell B1 (first row, second column).


    With that in mind, you can see that the n variable is set by the line "n = Range("P65536").End(xlUp).Row". This is then used to determine the upper bound of the For i = ... Next loop, which is the row variable in your Cells instruction. It is the j variable that is used as the second Cells argument, that is j represents the column variable.


    Therefore:
    1. Changing n has no impact on the columns used.
    2. The columns only go as far as P because j loops up to 16 (P is the 16th column).

  • not working


    Richie,


    As soon as i change "For j = 9 to 16" to anything other than a 16 it dosnt work. The next row "d = cells(i, J)" Highlights and i get the run time error 13.


    i dont think its anything i done, if i change it back to a 16 it works.


    by the way the grid line didnt work either, its just not my day!!!!!!!


    Bob

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!