Code to select & Fill blank cells in the subtotal rows

    Hello code experts

    I have this data in step2 sheet. With the help of a code I want to create a new sheet and name it as step3. I want to copy the data from step2 to step3, select the first blank cell in the data in column A till above the grand total row in the end and fill them with reference to the above row, color them yellow (as shown in sheet3). Then select the whole data, copy and paste special values to remove the formulas. Sort the data by yellow color on the top and delete the non color cells. Remove " Total" with the extra space from HSN Code column and format to normal. I have manually done all the steps via a code but in this step, as I was not able to select the blank cells with the help of a code. I need your expertise to complete Step3.

    Thank you in advance.

    Copy step2 to step3 fill blanks.xlsx

    Please note that the first blank cell in this data is A3 but it could be in any cell in some other data. If the code finds the first blank cell in the data then it will run correct in other data too. The columns are fixed from A to S. The number of rows too will be different in different scenarios. Hence, the code should select the row till below the last number in the A column (Above grand Total).

    • Best Answer

    Hello,


    Have you tried to turn on your macro recorder ... and go through your whole process once ?

    This would create for you a sound basis to work from ...


    Below is a quick macro to fill the blanks :

    Code
    Sub FillBlanks()
   With Sheet6.Cells
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Cells.Copy
        .Cells.PasteSpecial Paste:=xlPasteValues
   End With
End Sub

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    I did try recording a macro but the problem is that that macro will run correct only for the current data. If the data is replaced then it will not work as the blank cells may start from some other row.

    Carim. How do I color the rows containing Total in the HSN code.? Once colored I can delete the non color rows and get the required data.

    Carim. I have added your code to the code I was able to work.

    I want to delete the rows where the HSN code column doesn't contain Total.

    I am facing a problem. I want to copy only the data from step2 to step3 and not the sub total data.

    Once you have managed to fill in the blanks ... and replaced formulas by their values ...

    I do not understand why you are trying to add a color to some rows ...


    Seems to me you could Autofilter the rows to be deleted ...and as a consequence keep the ones you need...

    the ones where the word Total appears ...

    Code
    Columns("D:D").AutoFilter Field:=1, Criteria1:="<>*Total*"

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Pleased to hear you managed to fix your problem :)


    Thanks a lot for your Thanks AND for the Like 8)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quote from Carim

    Seems to me you could Autofilter the rows to be deleted ...and as a consequence keep the ones you need...

    the ones where the word Total appears ...

    This really helped me. It saved a lot of steps.

    Carim. I am facing an issue in one of the modules which was shared by you. There are columns filled with unwanted values after column S which have to be blank. Can you please check and help me correct the problem Please? Due to this the step4 to step6 sheets are also showing the same unwanted values. If you will help me correct this in step3 then automatically the other sheets will be corrected.

    I will share the file in some time.

    Actually. In the step3 sheet, the correct number of rows to display are up to row 34. The rest of the rows are unnecessarily derived how I am not aware. Maybe, in the code, if you select the data in the Step2 sheet from A1 to the end -1 row (Exclude Grand total row), then maybe the issue can be resolved.


    Unwanted data in column T onwards.xlsm

    Not sure to properly understand your step/macro sequence ...


    However, it would appear you are using .Cells which includes All Rows AND All Columns from the reference worksheet ...


    It is much safer to use a variable like lastrow, and column Q (as last column) along with the Sheet CodeName (Sheet9 or Sheet12)


    Code
    Dim lastrow As Long
lastrow = Sheet9.Cells(Rows.Count, "D").End(xlUp).Row - 1
With Sheet9.Range("A1:Q" & lastrow)
      .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
      .Copy
      .PasteSpecial Paste:=xlPasteValues
End With

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    It really helped me but I wonder why you wrote sheet9 instead of sheet12. Well, the issue in hand is resolved. Will let you know if I face any other issue.

    Thanks once again Carim.

    You are welcome.


    Thanks for your Thanks. :)

    Nice to hear you have solved your problem :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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

Register Yourself Login