Home » Forums » Open Source Learning Center » Question and Answer

delete blank cells and produce a new summary table

Submitted by amimo on 26 February, 2013 - 06:08

Hi :)

I have a big spreadsheet (1st column "100 student Names" and 1st row "Dates" which are from 1/1/2010 till 31/1/2013! and each cell contains a "grade" (A, B, C,...).

For example John had an "A" in 1/1/2011 and a "B" in 13/1/2013 and Nick had a "C" in 15/6/2012 and "A" in 31/1/2013 and the rest of cells are blank cells.

At the moment the spreadsheet is very bothering because there are a lot of blank cells which I do not like to see them and I need to have a VBA code to provide a summary table for each person like below:

John 1/1/2011 13/1/2013
A B

Nick 15/6/2012 31/1/2013
C A

So like when I select "John" then only Johns' grades appear against related dates and the blank cells in his grades' do not show up.

I am wondering you guys could help me with that.
:)
I appreciate your help in advance :)

I have attached a simplified version of my spreadsheet for your reference.

Looking forward to hearing from you guys.

M

AttachmentSize
sample.xlsx8.9 KB
sample_1_Nick.xls34 KB
‹ bisection vba code Recognising certain part of text then giving corresponding answer ›
»
Nick's picture

have a

Submitted by Nick on 26 February, 2013 - 08:18.

have a play: excelexperts.com/sites/default/files/sample_1_Nick.xls

I think ul like this.. in sheet1's module, there's the following code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Dim Col As Long
Application.ScreenUpdating = False
ActiveSheet.Cells.EntireColumn.Hidden = False
For Col = 2 To ActiveSheet.UsedRange.Columns.Count
    If Cells(Target.Row, Col) = "" Then
        Columns(Col).EntireColumn.Hidden = True
    End If
Next
Application.ScreenUpdating = True
End Sub
When you change your selection, it refreshes the area to only show the info you want.
»