Excel Conditional Formatting Rows to Columns
Solved/Closed
Related:
- Excel Conditional Formatting Rows to Columns
- Excel date format dd.mm.yyyy - Guide
- Marksheet format in excel - Guide
- Number to words in excel - Guide
- How to clear formatting in excel - Guide
- Excel free download - Download - Spreadsheets
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 25, 2011 at 04:26 AM
Apr 25, 2011 at 04:26 AM
try this and see sheet2
if you want to use the macro in your original file take a copy and keep it somewhere safely .
if you want to use the macro in your original file take a copy and keep it somewhere safely .
Sub test()
Dim r As Range, rfull As Range, filtrange As Range, cfilt As Range
Dim x, result As Range
Worksheets("sheet1").Activate
Set r = Range(Range("A1"), Range("A1").End(xlDown))
Set filtrange = Range("A1").End(xlDown).Offset(5, 0)
Set rfull = Range("A1").CurrentRegion
r.AdvancedFilter xlFilterCopy, , filtrange, True
Set filtrange = Range(filtrange.Offset(1, 0), filtrange.End(xlDown))
For Each cfilt In filtrange
x = cfilt.Value
rfull.AutoFilter field:=1, Criteria1:=cfilt.Value
Set result = rfull.Offset(1, 1).Resize(rfull.Rows.Count - 1, rfull.Columns.Count - 1). _
Cells.SpecialCells(xlCellTypeVisible)
result.Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = x
.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
End With
rfull.AutoFilter
Next cfilt
End Sub
