Hiding and unhiding rows

[jackfox68]

Hi All,

I have an excel spreadsheet that I use to send updates to a set group of people. After one of my analysts populates the data points they click on my "Run" button and it copies and paste the data into an email. One probelm I have is there are 10 rows I use to update attendance, however i do not always have 10 people out and when it copies the data over to the email it leave alot of white space. I would like it if only one row were visible below the last cell populated. If th cell then become unpopulated it would then hide the cell again. Can this be accomplished using VBA?

Thanks for any help in advance.

 

[pbornemeier]

Autofilter uses a title row, so the first row (1 in this code) will always be shown. This code assumes that data in column A indicates whether the row will be included and that the columns of importance are A through F.

This code will end with the columns A-F for the rows that had data in column A copied to the clipboard.

    Dim lLastDataRow As Long
    ActiveSheet.AutoFilterMode = False
    lLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1:F" & lLastDataRow).AutoFilter
    Range("A1:F" & lLastDataRow).AutoFilter Field:=1, Criteria1:="<>" 'Show only non-blank rows
    Range("A1:F" & lLastDataRow + 1).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

 

[jackfox68]

Hi Phil,

Thanks for the reply and the code. I will try to intergrate that into what I have now and see how it works. I am not sure it is going to give me the end result I wanted, however:
1. learned something new
2. appreciate the help

I am obviously a NOOB when it comes to VBA/VB, however I was wondering...do you think I should learn VB before learning VBA or if my goal is only to use it with Excel do you think VBA will be sufficient?

Thanks again for your help and I will let you know how I make out.

Brian

 

[pbornemeier]

If you let me know the range the full attendance cells occupy, I can tailor the code a bit more closely. Some of your current code would also aid in integrating the new code.

VB has more "stuff" than is required for VBA. There is a lot of overlap, but if you are going to be using VBA in office apps, then the extra stiff for VB is not required. You can learn a lot from the code on the forum and the books sold in the main site.