Posts by B Rama Krishna


    It is not clear on what basis would the comments be updated.

    Pl try the code below. It updates the comments in220728 OZgrid Add comments to cells.xlsm Sheets("Sheet1") from I9 to I1000 using the data from W9:Y1000. If this is not what you want, pl send file with sample data for better coding.

    Thanks Roy.

    This gives unpredictable results even with your file. I would rather use Subtotals.

    My references may not be right. Pl let me if I included all the relevant references. If it is ok, then we can close this thread. Otherwise, pl let me know. I have the office 365 latest version.

    I am trying to count a filtered row of records but have not been able to get a correct number from each sheet. Each sheet is filtered with the same criteria but would have a different number of records. Sample file attached with code. Would appreciate any help.


    Great that it takes care of your requirement.

    You do not need to mention anything about A column or range. For every cell in E column the procedure checks if it is a blank, if so the .offset(0,-4) code checks if there is any value in col A in the same row. If there is a value, then it will not allow the saving of the workbook till the corresponding E cell is filled up. If there is no value in A then, next E cell is checked.

    It would work for all the rows without mentioning of the Col A or the row number explicitly. Pl choose the correct worksheet and run it.

    Pl let me know if there is a problem.


    Your requirement is "If A&row has a value, workbook cannot be saved unless E&row is filled". If so, pl try the following code


    Use 'xlpasteall' to retain the formats

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' Just double-click on any cell in Source Row (Columns A to M)
    Dim l As Long, x As Long
    x = Target.Row
    l = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
        Sheet1.Range("A" & x & ":M" & x).Copy
        Sheet2.Range("A" & l & ":M" & l).PasteSpecial (xlPasteAll)
    Application.CutCopyMode = xlCut
    Cancel = True
    End Sub


    1. As you can see, there is no set pattern for the location of the dash based on the state. (eg. 'NJ' - sometimes there is no dash, sometimes it appears any where). Given this scenario, i do not think it is possible to factor that into the programme. Some senior member of this forum may have a better solution..

    2. For state 'AR', the dash appears in 4th position from right (at least in both the instances in your file). I have changed the code to insert the dash in the same position, if the state is chosen as 'AR' in Col F, L, R. You may enter the position against the state in sheet1 Col C. The state needs to be chosen before entering the vehicle number.

    The file is attached.


    1. The problem with dash appearing in the in the cells for the state abbreviations (assuming the abbreviation is contains only two letters) is sorted out. Also information entered (upto 2 letters) is converted into upper case.

    2 Is there a norm for the dash to appear depending on the state ? If so, pl indicate against each state in Col C of sheet 1 so that it can be incorporated

    3. Dash not appearing in cells happens if the application.EnableEvents is set to false (like exiting the sub routine midway - which can happen during debug mode). Also pl make sure that there are no blank spaces already before entering any data into a cell as the blank space is treated as a character.

    Pl check the attached file and give information on point 2 so that the same can be incorporated

    Hi ,

    Pl run the Splitcells subroutine. The unmerged data is stored in worksheet ("FinalData")

    It is preferable to have some data in each column.

    Pl let me know if this is what you are looking for.

    The file is attached.


    Hope the following meets your requirement. It introduces a "-" at a random position (2nd to 1 less than the length of the string) in the string.

    You can tweak the position of the dash if you have some other criteria for fixing its position.


    Pl let me know if my understanding is correct. For the cell values in Col A of sheet 1

    a) Retain rows with only 'Data', 'Date1' and 'Data2'
    b) From this data remove rows with 'Data' and 'Data2' (that leaves rows with only 'Data1')
    c) then perform the matching with Col A of Sheet 2 and delete non-matching rows

    In any case would appreciate if you could send an xl sheet and explain what needs to be done, so that I can work on the same.