Link to home
Start Free TrialLog in
Avatar of eemoon
eemoon

asked on

How to take out the data out in Excel ?

Hi Expert, There are a lot groups of data. below is two groups of data.  I want to take out the bold line and put them together. Any expert can give me some suggestion ? Thank you




interface GigabitEthernet2/9
 description  LAN
 switchport
 switchport access vlan 99
 switchport mode access
 shutdown
 spanning-tree portfast

interface GigabitEthernet4/4
 description - Inside
 switchport
 switchport access vlan 99
 switchport mode access
 spanning-tree portfast

i
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

What should the result look like?
Avatar of eemoon
eemoon

ASKER

Thank you so much for your fast reply!

It should look like following after them are taken out:

interface GigabitEthernet2/9
interface GigabitEthernet4/4
...
So this???

interface GigabitEthernet2/9
interface GigabitEthernet4/4
 description  LAN
 switchport
 switchport access vlan 99
 switchport mode access
 shutdown
 spanning-tree portfast

 description - Inside
 switchport
 switchport access vlan 99
 switchport mode access
 spanning-tree portfast
Avatar of eemoon

ASKER

Yes, that is what I want
OK then, one more question. How do I recognize the group headers (like interface GigabitEthernet2/9)? Is there a blank line in front of all of them except the first one?
Avatar of eemoon

ASKER

1, we want the first line in each group, and there is one line space between two groups
2, after the first line, all lines are indented
SOLUTION
Avatar of Naresh Patel
Naresh Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try this macro.

Sub GroupHeaders()
Dim lngLastRow As Long
Dim lngRow As Long

Application.ScreenUpdating = False

lngLastRow = Range("A1048576").End(xlUp).Row
lngNewRow = 2

For lngRow = lngLastRow To 2 Step -1
    ' Look for blank rows
    If WorksheetFunction.CountA(Rows(lngRow)) = 0 Then
        ' We found one so move the row below it (a header)
        ' to the top
        Rows(lngRow + 1).Select
        Selection.Cut
        Rows(2).Select
        Selection.Insert Shift:=xlDown
    End If
Next

Application.ScreenUpdating = True
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of eemoon

ASKER

I got it due to your suggestion, Thank all of you.
Just paste it into Excel and sort it!
Thanks dude but you allotted points to me which is not required as I dint even posted solution. niways see below solution
Function IsCellBold(cellx As Range) As Boolean
    If cellx.Font.Bold Then
        IsCellBold = True
    Else
        IsCellBold = False
    End If
End Function


Function isCellBackground(cellx As Range) As Boolean
    If cellx.Interior.Color = RGB(255, 255, 255) Then
        isCellBackground = False
    Else
        isCellBackground = True
    End If
End Function

Open in new window




See attached WB it has solution for this question as well as for your next question.

Thanks
EE-Question---Format-Sort.xlsm