Sorting Data

[JonathanEngr]

I'm trying to sort and extract data in excel and I'm not sure how/what the best way to do it would be. I will be using VBA for this project.

In summary, I'm trying to create a schedule that populates automatically for the instructors in our division so they can then print and post on their doors showing what time they have classes and office hours. Due to significantly varying levels of computer literacy, relying on the instructors to create their own schedule has been very unsuccessful. Attached to this post you will find the excel spreadsheet that contains the information I have to work with (Sample Schedule.xls). Also, there is a PDF showing a sample of what will be created with this information (Sample Schedule Final.pdf). The link for these files is below:

https://www.dropbox.com/sh/iax7grj5dq1bztp/AAA23RkJFd2i0-PbpHXL8LJQa?dl=0

I want this to be very, very simple for the end user. I will create a spreadsheet that contains a (hidden) worksheet of the information for our division (the Sample Schedule.xls file). On the primary sheet the instructor will simply choose their instructor ID from a dropdown list, and their courses and times will populate a pre-formatted schedule that runs from 7:00 AM to 10:00 PM. It will color code each class differently, as well as include a legend of the classes. I will have a command button they will click to save the schedule as a PDF. I can do all of this, but what I need is help extracting the data for each instructor ID. You can see there are many blanks below each course prefix, number and section. This is what's throwing me off.

Any thoughts, input and/or insights would be greatly appreciated!

 

[pbornemeier]

Re: Help Sorting Data in Excel

This code will fill each blank cell in the selected area with the filled cell data directly above it:

Sub CopyDataDownIntoSelectedBlanks()
    'For selected range, copy filled-cell data into blank cells below

    Dim rngInitialSelect As Range
    
    Set rngInitialSelect = Selection
    Selection.Cells.SpecialCells(xlCellTypeBlanks).Select
    With Selection
        .FormulaR1C1 = "=INDIRECT(""R[-1]C"",FALSE)"
        Application.Calculate
        Do While Application.CalculationState <> xlDone: DoEvents: Loop
    End With
    With rngInitialSelect
        .Value = .Value
    End With
    Set rngInitialSelect = Nothing
    
End Sub

You will have to reformat the date columns since the copied dates turn into integers.

Once that is done you can get a list of unique instructors with data and use autofilter to extract data for each instructor in turn.

 

[JonathanEngr]

Re: Help Sorting Data in Excel

Thanks!!! I actually tinkered around and came up with something that worked (but not nearly as svelte!). The code I used is:

Private Sub CommandButton1_Click()

Dim RowNo As Integer

RowNo = 1
For RowNo = 1 To 20
MsgBox RowNo
If Cells((RowNo + 1), 1).Value = "" Then
Cells((RowNo + 1), 1).Value = Cells(RowNo, 1).Value
End If
Next RowNo

End Sub

Other than having to tell it where to stop, are there any drawbacks to the code I used?

 

[pbornemeier]

Re: Help Sorting Data in Excel

It is only working with column A, but otherwise seems OK.

 

[JonathanEngr]

Re: Help Sorting Data in Excel

Yes--I only did a snip of the code to show you what I had done. I still consider myself somewhat of a novice at coding, so I like to get opinions on things I do when possible to learn more about coding efficiently, etc. Thanks!

 

[hiker95]

Re: Help Sorting Data in Excel

I still consider myself somewhat of a novice at coding

JonathanEngr,

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 12/19/2017

https://www.mrexcel.com/forum/excel...e-template-excel-spreadsheet.html#post4972842