I need to unhide 2 rows at a time starting with rows 28 and 29 IF cell N28:O28 > 0. I need this to be for rows 30 and 31 IF cell N30:O30 > 0, rows 32 and 33 IF cell N32:O32 > 0, rows 34 and 35 IF cell N34:O34 > 0, rows 36 and 37 IF cell N36:O36 > 0, rows 38 and 39 IF cell N38:O38 > 0, rows 40 and 41 IF cell N40:O40 > 0.
There is currently a formula in each of the selected cells that is why I was going for greater than zero.
I am attempting unhide the same exact rows in multiple worksheets in Excel, Print the entire workbook, and then re-hide the same cells. As I am still learning VB i have been unsuccesfull. I have attached the code that I am using,
Sub Printdoc()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
With sh
Rows("105:116").Select
Range("A105").Activate
Selection.EntireRow.Hidden = False
.PrintOut
Rows("105:116").Select
Range("A105").Activate
Selection.EntireRow.Hidden = True
End With
Next sh
End Sub
Using an external UNIX program that quieries a database, I export all the data found based on my query and export the report as a text file. THe text file can be up to 1 MB of raw text.
Selecting all text and pasting the data into collum 1 using PASTE SPECIAL -->(TEXT)
All Data is in Collum A
Each record returned has between 40-50 values each on a seperate row, only 10 of which I need.
I am looking for a way to find the rows that begin with the unwanted field, and then delete the entire row.
Otherwise ignore the row.
IE:
AUDFI : (data path loc here)
BILLNUM : 060606
CUTNUM : (0000)
SD : 120012
ED : 121259
CC : 123456789 MATERIAL LISTS AND ITEMS
COMMENT : Multiple lines of text here
with continuing free form data, numbers, operators,
then the next data file would repeat:
ADUFI : (data path loc here)
...
...
...
etc.,
In the above example, I want to delete the rows AUDFI, & CUTNUM and move the below items up to take the empty rows place. The BILL NUM, SD,ED, and COMMENT are filed that are always kept (as well as others). Each record has roughly the same data, each tailored to an event or object.
The number of rows of data dumped into excel can easily exceed 50000, about 200 - 1500 records in the report.
After wards, the data is dumped to a text file for easier analysis.
In BASIC my line might look like
5 START
10 if A:$="AUDFI :" then delete row, move below contents up.
20 if A:$="CUTNUM :" then delete row, move below contents up.
30 END
(I think there needs to be a loop since the vaules AUDFI and CUTNUM (as well as other unwanted fields can occurr 200 - 1500 times each.)
I use the following code to copy entire rows from one sheet to another and this is pretty standard.
VB : rng.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Assuming that Sheet2 is blank, this of course copies the rows to Sheet2 starting at Cell A2 and down column A. I would like to modify the code so that I can specify at which row the copy should start. For example, I might want to start the copy at A1 instead of A2, or at A10 instead of A2.
I saw this macro posted by Fengore back in 2006, and it works beautifully. Now my question: Is there a way to continue using that string but have it start from a certain row? Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
View 2 Replies View RelatedI need to repeat the following rows and formula with column a starting at 9150 and going through 15,000 in 50 point increments. Need formula I can copy and paste easly. see the attached example.
View 4 Replies View RelatedI have a spreadsheet where I track resources needed on a project. Each resource that is selected in the resource column has a corresponding % avg and an average number of months where the resource is used. % Avg and # of Months are found in a different worksheet.
What I am looking to do is, for example: if resource A will be on the project starting in May 2015 and assigned at 30% for 7 months, I want to populate 30% starting in the colunm labeled May 2015 and copy the 30% so that a total of 7 months are populated. Also, if any of the values change (i.e: the Resource Name or the Start Month) I want to erase all the percentages that were entered in the row. How can I achieve that with a VBA code? I have formulas that retrieve the # of Months and the % Avg already.
These are the columns that I have in my spreadsheet:
Resource NameStart MonthJan-15Feb-15Mar-15Apr-15May-15Jun-15Jul-15Aug-15Sep-15
I need to accomplish selecting a range using variables for the starting cell and ending cell rows and columns.
Pseudo code (how I'd like the syntax to be, but isn't):
Worksheets(mysheet).range(startCellRow, startCellCol : endCellRow,endCellCol).Select
With Selection
...
End With
Using excel 2007. I am interested in writing a VBA code to delete rows based on the text starting content. I would like to delete rows with cells that do NOT start with an "S" or "SA"
EX:
05S0128
06S0112
05S2298
S25852
S36963
SA36185
I would only like to keep the last 3 lines.
I highlighted a selection of rows and clicked HIDE, now I want to unhide certain rows and when I highlight the rows above and below and right click - unhide nothing happens. I need to Unhide to find something.
View 2 Replies View RelatedIs there a VB routine that can unhide rows when I print?
View 9 Replies View RelatedI have a worksheet with a macro as follows: ...
View 6 Replies View RelatedIs there an easy way to make a macro to unhide all rows? I can do this to unhide a certain range however different rows maybe he hidden and so a set range won't work.
View 2 Replies View RelatedI am trying to create a macro to unhide only a row at a time, from around 150 hidden rows altogether. It is basically to give the effect of adding extra rows to a "table" that a user could then input with new data (I have to do this way according to other set-up in the workbook), but this isn't very important to do with this question. When I do a Format<Row<Unhide from the Excel menu, it always unhides ALL of the hidden rows. I have fiddled around but can't seem to find a way to avoid this happening, all I want is for just one row to unhide. I thought it might depend what cell/s I had highlighted, but I haven't found a way that makes it work.
View 7 Replies View RelatedI HID 4 rows using this help section. (Format - Hide)
Then, I modified several column widths simply by placing my curser at the top of the column and pulling it smaller or larger.
(I don't know if this did or did not cause my problem) which is simply that - I am now unable to find my hidden rows.
I know where they are supposed to be, but when I highlight them and click (Format - Unhide) nothing happens.
I tried (Find and Select - go to special) and no white bar shows up.
Can sombody please tell this frustrated Newby How to find the rows I just hid 1 hour ago?
If I posted in the wrong place, I appologise as I spent more time trying to figure out where and how to post than I did trying to solve my hidden row problem.
I have done a search and drew a blank on this one. Hopefully there is an easy answer otherwise I see this as a fundamental flaw in Excel 2007 (on top of the lost productivity from ribbon interface). In all previous versions on Excel it was possible to select the rows either side of a hidden range (i.e. including the hidden rows) and right-click, Unhide. In Excel 2007 only visible cells are selected so this method does not work.
Hovering the mouse pointer on the row headings at the point of the hidden rows, waiting for the cursor to change and then right-click, Unhide only unhides the row imediately above, NOT the entire range. It seems the only way to unhide the entire range is to enter an address in the Name Box (taking care only hidden cells included otherwise only the visible ones will actually be selected) and then Format > Row > Unhide or Format > Row Height
I've a few rows that are hidden, and I cannot unhide them using the unhide function. The spreadsheet is not protected. What can I do?
View 5 Replies View RelatedI have a button that I want to hide/unhide a section of rows.
This is the code a have so far:
[Code] .....
So I've named a a section of rows "rngHideRows". I've tried to use the command .Range("rngHideRows").EntireRow.Hidden = True (and viariants of this) but nothing I try works.
code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Sheet2")
Select Case Target.Address(False, False)
Case "B12"[code].....
I need to change it so that it hides a range of rows, say (6:26) instead of adding a line of code for each of the rows from 6 to 26 that I want to hide
Also how can I go about hiding the same rows on 5 more sheets, can I add more sheetnames after "Sheet2"?
Need a macro which will hide rows having 0 values. On running once it will hide those rows having 0 values and on pressing second time it will unhide those hidden rows and so on.
View 14 Replies View RelatedI need to hide / unhide some rows in a sheet with the help of a activex checkbox... I want remove, let's say row 20:24 and row 34 with one checkbox.
View 6 Replies View RelatedI have a code that will hide all rows that have a "-" in Colum A, leaving me with the useful data.
However, this data is pulled from a front sheet that has data added to it.
What I would like my code to do is to relook at all the hidden cells, and if their value has changed from "-" to what ever the data may be then to unhide that row, displaying the data.
The code I'm using currently is: ....
I have a list box on a worksheet that contains the following names: field, vessel, pipe, structural. I would like to be able to unhide certain rows based
on the name shown in the list box. For example, if I choose field it would
unhide rows 3 thru 12, if i choose vessel it would unhide rows 13 thru 20, so on so forth. If nothing is choosen in the list box then rows stays hidden.
i'm newbie here. i need to hide & unhide rows using checkbox in excel but i stuck. i write these simple code and it doesn't work at all. my bad..
View 2 Replies View RelatedIs it possible to unhide a row in one sheet and the same line can be un hidden automatically ini 2 other sheets within the same workbook
View 1 Replies View RelatedI have a workbook with multiple tabs. Each tab has about 600 rows. In each tab, I need to hide/unhide rows based on the value of a particular column in that row. For example,
Row1: 10,7.89,John,TRUE
Row2: 16,9.08,Ram,FALSE
Row3: 98,8.09,Joseph,FALSE
Row4: 76,1.23,Harry,TRUE
Using the 4th column (that has either TRUE or FALSE), I need to hide entire row. I am using a loop on the range and hiding each row. But it is taking about 4 minutes for each tab to loop through the 600 rows and hide/unhide the required rows. Is there a faster way to achieve this? Following is my code.
Sub Toggle_Rows()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Range("D1:D600")
If UCase(Cell.Value) = "TRUE" Then
Cell.EntireRow.Hidden = Not Cell.EntireRow.Hidden
End If
Next
Application.ScreenUpdating = True
End Sub
I think this is fairly straight forward but my limited VBA skills have me going round in circles. I would like to have a button that hides and unhides rows where Column L has blank values but only from row 80 to 200 only. I would also like the button text to change from "Hide rows" to "Unhide Rows".
From Row 80 to 100 hide rows where there is no value in column LButton should be clickable to hide rows and then clickable to unhidebutton text changes.
I am struggling to come up with a vba code that allows me to search column B, Rows 21:89 for blanks then hide/unhide the associated row. I would like it to be one macro so that I don't have to have two buttons on the sheet to hide/unhide.
View 5 Replies View RelatedI have a macro that works fine with just numbers as a test run when I use it in the desired workbook/worksheets (where the values are returned via Indedx/match functions) there appears to be a small problem. (Below is the relevant post.)
[url]
Background is that I load raw data into a monthly account sheet which has the appropriate cost centre codes for allocating expenses.
I then use a vlookup in combination with indirect to place the summary data by costcentre code into annual worksheet.
I then have a summary sheet for printing purposes so I can print out a single month expenses by code to staple to the appropiate monthly costcentre statement. This is done by using Index & match functions looking at the annual summary sheet
What seems to be happening is that the code is sometimes recognizing formulas as a value & therefore showing zero values when I want only values 0 (as I sometimes have minus values).
Also sometimes when I go to unhide the all the rows it also is recognising some formulas as having value.
I am using Xl 2003.
I'm using this VBA code which is hiding cells in rows 59:111. Some of these cells have formulas and some don't.
Private Sub Hide_Unhide()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 59 To 111
Rows(i).Hidden = (Cells(i, "b").Value = 0) + (Cells(i, "b").Value = "")
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Here is the problem I'm running into: I want this to run in the background so the user won't have to click anything to hide/unhide the cells whenever the options in the drop down list are changed and the data expands/collapses.