Free Microsoft Excel 2013 Quick Reference

Cannot unhide a hidden row in Excel

Hi. I have a row in a spreadsheet in Excel that is hidden but i am unable to unhide it. I have tried the usual way by hovering between the rows above and below until i get the double line but this does not happen. I have also tried going through the Format - Row - Unhide option but this also doesn't work. Any suggestions?


what is the shortcut to insert a new row in excel??

Hi,

I am running into a problem specifying a mail merge range in Excel 2002/2003 that only includes non-blank cells. In Excel/Word 2000 I was able to achieve this by hiding unused rows in Excel before performing the mail merge. This also added simplicity to the Excel “form” by showing the user only rows needing to be filled out at the time. In Excel/Word 2002 and beyond, this no longer seems to work, however, one peculiarity seems to make me thing perhaps Excel 2002 and beyond is still capable of limiting a mail merge data source range in this way.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

The actual mail merge is being performed using Microsoft Word VBA code (as I ran into far too many problems in executing the mail merge in Excel) while the Excel VBA code is used for showing/hiding cells, clearing the form, determining the number of printed pages in the final step, and saving and continuing the next step in the mail merge process.

Full details to follow and I’d be more than happy to upload screen captures if that would be of help. If you believe this to be a Word VBA solution I will try and find a Word coding forum in which to ask this question.

Thanks much!

-------------------------------------------------------------------------------------

A year ago I came up with an automation “mini-app” that staff can use to generate the correct number of student aide tags needed for the student aides helping them in their classes. This automation uses a batch script, Excel VBA and Word VBA to complete a mail merge in Microsoft Word.

The batch file copies the Excel and Word documents to the user’s Desktop, launches Excel first, inviting staff to fill out the required columns “TEACHER’S LAST NAME,” “STUDENT’S FULL NAME,” “PERIOD # (INCLUDING SUFFIX),” and “TYPE OF TAG.” There is a textbox in column F that allows users to show or hide cells (eight cells are shown by default), a button to clear the form, a button to find out how many printed pages they should expect (based on a mathematical formula that divides the number of shown cells by eight and another formula that divides the number of filled cells by eight) and a button that saves and closes the Excel document.

Upon clicking the “Save, Close and Continue” button in the Excel spreadsheet, the batch script launches the Microsoft Word document on the Desktop where Word VBA code automatically completes the mail merge with the Excel document on the user’s Desktop and initiates the printing of the completed merged document to the default printer, closes the Word mail merge template document and (upon closure of the Word application) deletes the temporary documents off the user’s Desktop.

The version of Microsoft Word used in our building prior to the upcoming school year was Microsoft Office 2000 (the OS is Windows XP). However, we recently upgraded all of our licenses to Microsoft Office 2003 and I soon realized that I also needed to make a few adjustments to my “mini-app” in order for it to work with the upgraded version of Office.

Everything is now working as it should except for one interesting little bug. In column F of the Excel spreadsheet I added a textbox with some VBA code to hide/show rows in the spreadsheet. By default, only nine rows (eight plus the header row in row 1) show as there are eight student aide tags to a printed page once the mail merge has been completed in Word. The instructors can use the textbox to hide or show additional rows as they need to. This works to simplify the user’s view by allowing them to only show the number of rows needed but also, in Excel/Word 2000, creates a situation where (by default) Word only performs a mail merge against the unhidden cells. This limits the number of pages printed and eliminates the unnecessary printing of blank pages following the completed mail merge page(s).

The rub is this. In Excel 2000 hiding unused rows eliminates the blank pages from being generated in Word by limiting the data source range. However, in Excel 2002 and beyond, hiding rows does not limit the data source range in Word and a number of blank pages (based upon the number of rows unhidden in the first place) are generated following the final mail merge. It doesn’t seem to make a difference if I manually rehide the rows or rehide these using the VBA code associated with the textbox.

For example, the Excel document starts with only nine rows unhidden, and if I precede without unhiding any additional rows only one printed page is generated in Microsoft Word. However, if I unhide say the first 50 rows and then, prior to saving and continuing, decide to rehide all but the first nine rows, six additional blank pages are still generated in Microsoft Word upon the completion of the mail merge. Blank pages occur when unhiding more rows than the user fills out.

What I find interesting about this is that there has to be something saved in the Excel 2003 document upon hiding/unhiding rows. I can temporarily rectify the additional blank pages problem by copying the original, untouched, Excel document and reapplying the mail merge. So I’d deduce that there must be something saved in the original, untouched, Excel 2003 document that allowed Word to limit the data source range to only the non-hidden cells; There must be something that changes in the Excel document when I unhide/rehide cells? However, I have not yet been able to duplicate the results.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

Thanks much!

-Matt-

----------------------------------------------

Here is the VBA code from the Microsoft Excel file:

Excel - Sheet 1 (Generate Aide Tags App):

Option Explicit
Private varNumCellsToShow
Public varReturnDetected

Private Sub btnHowManyPrintedPgs_Click()
Call HowManyPrintedPgs
End Sub

Private Sub btnNumStudsEnter_Click()
On Error Resume Next
Application.ScreenUpdating = False
varNumCellsToShow = txtNumCellsToShow.Text + 2
If txtNumCellsToShow.Text = 65535 Then
MsgBox ("You entered a number greater than or equal to 65535. Showing all 65535 rows.")
txtNumCellsToShow.Text = 65535
varNumCellsToShow = 65536
Range("B1").Select
End If
If varNumCellsToShow = "" Then
Exit Sub
End If
Cells.Select
Selection.EntireRow.Hidden = False
Rows(varNumCellsToShow & ":65536").Select
Selection.EntireRow.Hidden = True
varNumCellsToShow = ""
End Sub

Private Sub btnSaveAndClose_Click()
ActiveWorkbook.Save
Excel.Application.Quit
End Sub

Private Sub bttnClear_Click()
Application.ScreenUpdating = False
ActiveWindow.SmallScroll Down:=-33
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B2").Select
End Sub

Private Sub txtNumCellsToShow_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
varReturnDetected = 1
Call btnNumStudsEnter_Click
End If
End Sub

Private Sub txtNumCellsToShow_LostFocus()
If varReturnDetected 1 Then
Call btnNumStudsEnter_Click
End If
varReturnDetected = 0
End Sub

Excel - Module 1:

Option Explicit
Private mycount As Long

Sub HowManyPrintedPgs()
Dim FinalRow
Dim LastFilledRow
Dim NumberofPages As Integer
Call NumberUnhiddenBlank
If mycount = 0 Then
FinalRow = (Range("B65536").End(xlUp).Row)
If FinalRow 7 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 student aide tags to a page and you have elected to print " & FinalRow - 1 & " tags. Tags will print to " & NumberofPages & " pages.")
End If
End If
If mycount > 0 Then
FinalRow = (Range("B65536").End(xlUp).Row) + mycount
LastFilledRow = (Range("B65536").End(xlUp).Row)
If FinalRow 2 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 tags to a page, you have elected to print " & LastFilledRow - 1 & " tags. IMPORTANT NOTE: You have " & mycount & " blank rows at the bottom of your form. To reduce the number of pages printed, please be sure to adjust your Number of Tags to Create textbox settings. Tags will print to " & NumberofPages & " pages.")
End If
End If
End Sub

Sub NumberUnhiddenBlank()
Dim c As Range
mycount = 0
For Each c In Range("B1:B65536")
If Rows(c.Row).Hidden = False And c.Value = "" Then mycount = mycount + 1
Next c
'MsgBox ("The number of unhidden, blank, rows is " & mycount & ".")
End Sub

Here is the VBA code from the Microsoft Word file:

Option Explicit

Private Sub ActivateMailMerge_Click()
Call Document_Open
End Sub

Private Sub Document_Open()
Dim UserName
UserName = Environ("username")
MsgBox ("This step will take a couple of seconds. Please hang on...")
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:Documents and Settings" & UserName & "DesktopStudent Aides.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
:="", SQLStatement1:=""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

Dim NumPgs As Long

NumPgs = Selection.Information(wdNumberOfPagesInDocument)

If NumPgs < 2 Then
Select Case MsgBox("Please insert " & NumPgs & " page of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

If NumPgs >= 2 Then
Select Case MsgBox("Please insert " & NumPgs & " pages of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

End Sub

Hello,

I have been practicing with pivot tables and hid a few row sections. But I
have not been able to unhide the row. Is there a way to do this? or do I
have to recreate the pivot table?

Thanks,
Rich

Not sure if this is an Excel or Word problem...

I am doing a mail merge in Word (envelopes). I am pulling my data (name,
address, cit/state/zip) from an Excel .xls I created.

In mail merge, I select envelopes and go through all the steps.

I use the Address Block for the delivery address. However, when it pulls
the data over, the name line (first line of address block) is indented by one
space. No matter what I do (place cursor at indent and use backspace or
place cursor at next two correct lines and add a space), the change doesn't
take place. I've checked all the tabs as well.

Someone mentioned that I might have a hidden space in the cell but when I
look at my cells, I don't see anything obvious. Any ideas?

Hi there,

I'm using excel 2007. In worksheet i have only 65,000 rows. I have heard
that excel 2007 can provide over a million rows. Can some one help me on how
to get all the rows (over a million rows) in excel 2007?

Looking foward to hear from you.

Thanks

Malaki

I have inserted a macro button that unhides one row at a time in a specific place on a worksheet. The goal is to provide extra lines in a section of a form. The initially hidden rows span from row 40 to 53 and stop at a cell named "Total." When the button is clicked, one row is unhidden beginning with the smallest row number. So if 40 - 53 are hidden, the macro 1st unhides row 40, then 41 etc.

I now need to insert a button to HIDE one row at a time but I need it to hide the LARGEST row number in the range. For example, if rows 40 to 50 are unhidden, I need the button to hide row 50 - not row 40.

The initial Unhide macro is as follows:


	VB:
	
 UnhideOneMoreRow() 
    ActiveSheet.Unprotect 
    Dim TotalRow As Long 
    TotalRow = Range("B:B").Find("Total").Row 
    For ThisRow = TotalRow - 1 To 1 Step -1 
        If Rows(ThisRow).Hidden = False Then 
            Rows(ThisRow + 1).Hidden = False 
            Exit For 
        End If 
    Next ThisRow 
     
    ActiveSheet.Protect 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can you help me write the inverse?

How do you retrieve a whole list of cells in a row in excel?

whats the keyboard short cut for inserting a row in Excel , deleting a row

I am trying to create heading row in excel that would appear on every page. I
want it to be possible to see this heading row as I scroll down and also want
it to print on all pages when I print.

I am referring to a heading row that I create and not the computer-generated
(A, B, C ...) heading row.

Thank you much for your assistance.

A new problem in Excel - when I hide a row, suddenly I can't use the "unhide"
menu command to unhide the row. I have to use the tool and pull it down and
resize it. Anyone experienced this? Thanks

I'm having trouble deleting rows in Excel 2007 using filters. I have a large
volume of data from another program which I import in Excel and a simple
macro went through and deleted all the garbage lines. I encountered a
problem moving from Excel 2003 to 2007 as when highlighting all the rows it
deleted all of them not just the visible rows. I started to overcome this
using the Show visible cells option, but I'm now getting the message
"Microsoft Office Excel cannot create or use the data range reference because
it is too complex".
I would happily go back to Excel 2003, but I have a lot more than 65,000
rows of data.
Any other suggestions?

hello
i need some help with deleting Rows in excel using a do loop

my program looks like this
Sub Cleanup()
Dim r1 As Range
Dim r2 As Range

Set r1 = Cells(6, 2)
Set r2 = ActiveSheet.Rows("46:52")

Do While r1.Value ""
r2.Select
'Selection.Delete Shift:=xlUp

r2.Font.Bold = True
Set r1 = r1.Offset(47, 0)
Set r2 = r2.Offset(47, 0)

Loop

End Sub

The problem i am having is when i make the things i want to delete bold
they do become bold but when i want to delete those rows it does for
the first row and then stops and says "OBJECT REQUIRED" i known i make
making a mistake in the code somewhere but not getting where

thank you in advance for your help/advice

Regards
Indraneel

Sorry if I'm in the wrong place to ask but does anyone have any idea how to unhide a very hidden sheet in Excel for Mac? I normally work on my PC but when travelling I use a Mac. Pulling my hair out here!

How do I 'UNHIDE" A COLUMN/S IN eXCEL?
pLEASE RESPOND.

How do you copy an entire row in excel to a new tab based on a cell value. Then delete the cell in the first sheet

A new problem in Excel - when I hide a row, suddenly I can't use the "unhide"
menu command to unhide the row. I have to use the tool and pull it down and
resize it. Anyone experienced this? Thanks

Hi friends do you know that What is the intersection of a column and a row in excel?

Macro to select rows in excel.

Once the routine is running I wish to select a number of rows, which may or may not be adjacent to each other. Then the rows are copied to clipboard.

Thanks,

Sandy

I have a bunch of filtered/hidden rows in my source sheet for a pivot table. I do not want these rows included in my pivot table calculations. Is this possible?

Hi All

Is there a way to auto delete blank rows in Excel...or maybe using SQL?

I´m trying to insert a bricscad drawing in Excel and Excel reports that
cannot insert object.
With AutoCad it works perfect.

Need fixed length cells and rows in excel spreadsheet saved as space
delimited .prn file. The .prn file will be FTP'd to Mainframe computer file
(which has fixed length fields (each cell = a field in Mfrm file) in a fixed
length record. Is there a way to format cells as text data, with a fixed
number of spaces in the cell? Then the client could overtype the spaces with
the required data.

Hi!

I'm having the issue described in KB918621 where hiding rows in Excel 2003
causes a recalculation of the entire worksheet. Since my spreadsheet
contains 1500 rows and nearly 840 subtotals, this takes close to 20 minutes...
I could use the suggested workaround of disabling automatic calculation but
this is a bit extreme.
The only alternative I can think about is to set up macros to turn this
feature on and off and link them to toolbar buttons.
Can anyone think of anything better?
Thanks in advance for any help you can offer.

Best wishes,

Andrew

I've created a pivot table in Excel, and I would like to format it so that
the "grand total" rows are reversed out. Is there a way to select an
autoformat and customize it OR is there a way to easily do some conditional
formatting. This is a report that is summarizing a resource's total days
working on a project, and there are about 400 resources, so I certainly don't
want to format each row.