Insert description & hide rows

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
882
Hi all, I would like to insert the description from row above into adjacent cell below in col."B" and then to hide the rows which have the same code in col."A" Please see below the sch. 1. are the original data and in sch.2. is the expected result. Thanking you in advance

Sch.1.

AB
1Code
Description
27800005TOURIST
37800005TOURIST
47800005TOURIST
57800005 Total
67800048CONFERENCES
77800048CONFERENCES
87800048CONFERENCES
97800048CONFERENCES
107800048CONFERENCES
117800052 Total
127800077WEDDINGS
137800077WEDDINGS
147800077WEDDINGS
157800077 Total

<tbody>
</tbody>



Sch.2.

AB
1Code
Description
57800005 TotalTOURIST
117800052 TotalCONFERENCES
157800077 TotalWEDDINGS

<tbody>
</tbody>
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try:
Code:
Sub FilterRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Range("A1:B" & LastRow).AutoFilter Field:=2, Criteria1:="="
    For Each rng In Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible)
        rng = rng.Offset(-1, 0)
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this to fill values and hide rows.
Code:
Sub t()
Dim sh As Worksheet, lr As Long, c As Range, rng As Range
Set sh = ActiveSheet
lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    
    For Each c In sh.Range("A1:A" & lr)
        If Right(Trim(c.Value), 5) = "Total" Then
            c.Offset(, 1) = c.Offset(-1, 1).Value
        End If
    Next
    sh.Range("A1:A" & lr).AutoFilter 1, "<>* Total"
    Set rng = sh.Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow
    sh.AutoFilterMode = False
    rng.Hidden = True
End Sub
 
Upvote 0
Hi mump, it hides all rows and further it appears msg box 'Run-time error 1004', No cells were found. However many thanks for your support and is appreciated . Have a nice day
 
Upvote 0
Many thanks JLG, it works perfect and exactly what i was required. Thanks also for your time spent for my project. Have a lovely day
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top