Hide Rows Or Columns
An interesting code, for us working with complex spreadsheet, would be to know how to create a macro that would remove an entire row containing ZEROS in the cells. In example below, only the Account#70200 row should be hidden (NOT the 70400).
Example:
Account# January Feb March
70100 1,000 2,100 3,000
70200 0 0 0
70300 1,200 1,100 2,000
70400 0 2,100 2,100
Similar trick for hiding columns with zero or no values would be very usefull.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Hide The Columns And Rows
Hai gurus
Can I hide some(not all not thru width =0) columns or some rows in the list view?
regards
Rajender G
How To Hide Columns And Rows Not Needed?
Hi, i would like to hide the columns and rows that i dont need when the workbook is open.
I was thinking a For loop from B3 - the last column, but how do i find out the last column?
Same goes for the row.
Also what is the command to add a row or column.
Thanks a bunch..
Hide Rows
I wonder if someone can help me with a macro that I can't get my head around
I would like a macro that hides rows if the first cell in that row contains a 0. Example
A1 = Hello
B1 = 0
C1 = Computer
D1 = 0
E1 = 0
so rows that would be hidden are B, D & E
The 0's are as a result of a lookup where the results are empty. Hope that makes sense.
Many THanks
Hide Rows
Is there a way to put a forumla into a range of cells and hide the rows, but when the formula calculates the row becomes unhidden. For example if I were to put a group of number in Column A 1,2,3,4 and have a SUM(A1:A4) in Row 5, but I have row 5 hidden until I enter something in rows 1-4. Is this possible?
Rows Hide
i want to select a couple rows, for example row 8,10, 12,14,......66
The selected rows must be hidden. The following code should select and hide the rows 1 by 1. The code doesn't work and i don't know why
Sub xtraRegelsVerbegen()
' Macro voor het invoegen van Xtra regels
Dim i As Integer
For i = 6 To 66
Worksheet.Range(i, i).Hidden = True <-- here stops the debugger
i = i + 2
Next i
End Sub
Werner
Hide Rows Automatic
Hi
Can you please help me with this Excel problem.
I want to hide som rows depending on what the value is in a specific cell.
If the value in cell D2 is 1, I want to hide row number:20, 19 and 18
If the value in cell D2 is 2, I want to hide row number:20 and 19
If the value in cell D2 is 3, I want to hide row number:20
If the value in cell D2 is 4, I want to hide row number:nothing
Can you please help me with the VBA codes?
Looking forward to your help
Sorroo
Need To Automatically Hide Rows W VBE
I am trying to use Visual Basic to automatically hide rows in Excel. Basically, I have a sheet that has 50 rows (A12:A62)…each cell in column A is linked to another sheet and comes back as a 0 or a 1 depending on pull-down options throughout the workbook. If the link comes back as a 1, I want the entire row to hide, otherwise, show the row.
Also, I don’t want to have to run a macro. I want the rows to hide/unhide whenever someone is looking at that sheet and changes some criteria. I put the following code and it works if I manually change the value in A12, but when A12 is linked to another cell, the row doesn’t hide when the value changes.
Private Sub Worksheet_change(ByVal Target As Excel.Range)
If Target.Address = "$A$12" Then
If Target.Value = 1 Then
Range("a12").Select
Selection.EntireRow.Hidden = True
Else
Range("a12").Select
Selection.EntireRow.Hidden = False
End If
End If
End Sub
Does anybody know how to make this work when A12 is linked? Also, ultimately I will have to make this apply to 50 rows (A12:62). How would I do this without repeating the above code for each row? Thanks
How Can I Hide The Rows In MSFlexGrid
hi
in my application i am using the Flexgrid. so how can i hide the rows. i need to hide some records depends on the condition.
one thnig i can do is rowheight=0. but in that rows i have pictures, bcoz of that its not possible that chance too.
plz help i need urgent.
DBGrid - Hide Some Rows
Here is the thing...
I have a form with my dbGrid that is linked with data from Access 97...
Now I have a textbox and a button...
I want to input something into my textbox and if it finds it in second field in my database I want it to hide from dbGrid.
I know how would I do that, but I can't figure out the way to hide something from dbGrid, and that I dont delete it from my database. Tnx in advance
How To Hide Rows In MSFlexigrid
hi
in my application i am using the MSFlexgrid. at the start up i am loading all the records into the grid. what i want is that when user selects some creteria, then depend on that i want hide the some rows. as i my knowledge i think it wont be possible. what we can do is that we can specify the rowheight=0. but thing here is that i have pictures in these rows, when i say rowheight=0 then the pictures are coming up a little view. so is there any way so that i hide or display the rows in the flexgrid. at present i am using the flexgrid. is there any control which will give option for hiding the rows.
thanks
---------------------------------------------
Srinivas.
Hide All Unused Columns
Servus again,
I want to integrate a macro, that hides all unused (=empty) columns. But: the empty column, that follows directly to a not-empty column, is supposed to be unhided.
E.g.: A is empty --> hide. B is filled --> unhide, C is empty --> unhide because it follows directly to B ...
The code does not seem to work, but i don't know why... Any idea??
Here is the code:
Sub LeerKiller()
Dim row, col, dist, cont As Long
dist = 0
cont = 0
For col = 1 To 300
For row = 1 To 65000
If Not Cells(row, col) = "" Then
Columns(col).EntireColumn.Hidden = False
cont = cont + 1
Exit For
End If
Next row
If cont = 0 Then
Columns(col + 1).EntireColumn.Hidden = True
End If
Next col
End Sub
MSHFlexGrid Hide Columns
I am filling a mshflexgrid with a recordset of 4 fields. I only need to show
two of the fields in my flexgrid, and the other two fields I only need for calculation. How can I handle this ?
The grid have 4 columns; column 1 and 2 should be from the recordset, and
column 3 and 4 is additional fields. Hope someone can give me some good ideas.
How To Hide MSFlexGrid Columns
I use flex.ColWidth(5) = 0 to hide the column five but I really want to hide a column allowing the user to resize only the columns wich are not hidden. If you position the mouse between 2 colums and there is a hidden colunm between them you can resize it and becomes visible which is not what I want. Any idea will be appreciated.
Is There A Way To Hide Listview Columns?
hey guys I have a listview called report. Is there a way to hide the first column, so thats its there and the program can access it, but just hidden from the user?
Thanks!
Hide Columns In DataGrid
Dear friends,
I have 5 fields in Table Items.I created DataGrid to view the table.
I have 5 cols. as follows.
code,name,uom,desc and stock.
But I don't want to display all the columns.I want to display only
code,name and stock.How to hide other cols, Please help me to do!
DBGrid Hide Columns........
I have a DBGrid with Columns that I require, however I change the RecordSource to the DBGrid on several occasions this is to save hiding three diferent DBGrids.
As you can Imagine there are unused columns showing in the dbgrid which means you have to scrole to see parts of the data.
Is there a way to hide columns on run time?
thx
Hide Columns In Datagrid
Hi! i use a recordset to set datagrid datasource.
as below,
rs.open "select ID,name,Desc from table" 'rs recordset
set dg1.datasource=rs 'datagrid dg1
My problem is how to hide 'ID' field from the datagrid.
I dont want to display that column.
I know there's a property named 'visibleCols' in datagrid.
it gives the number of columns displayed. How can i put it
to use to hide a column. Any other alternative too will be
great!.
thnx
Mur.
Columns && Rows
I loaded a lot of pictureboxes at runtime where I will later paste pictures into. But before that I need to sort the pictureboxes into Columns and Rows. How can I put the picboxes 1 to 10 in Row 1, the picboxes 11 to 20 in Row 2 and so on?
Rows And Columns
I tried using the following code to get the total number of columns and rows from an excel sheet but it didn't work...can anyone help?
VB Code:
Private Sub cmdLoad_Click(Index As Integer)If FileName = "" Then Dim Response As Integer Response = MsgBox("Please open an excel file.", vbOKOnly, "Error")Else Dim oWB As Excel.Workbook moApp.Visible = True Set oWB = moApp.Workbooks.Open(FileName) RowCount = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row ColumnCount = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column Text1.Text = oWB.Sheets(1).Cells(Row, Column).Value txtColumns = ColumnCount txtRows = RowCount oWB.Close False Set oWB = Nothing moApp.Visible = FalseEnd If
Rows And Columns?
HI
I am working on a calendar program where I built some rows and columns but this was using an array with top width height and left positioning.
It looks like that I am taking a long road that comes with a world of hurt.
Is there a better way to do Rows and Columns?
Here is my code:
Code: On Error Resume Next
Dim i As Integer
For i = 0 To 41
Load Text1(Text1.UBound + 1)
With Text1(Text1.UBound)
If Text1.Count <= 8 Then
.Move Text1(Text1.UBound - 1).Left + Text1(Text1.UBound - 1).Width, Text1(Text1.UBound - 1).Top
.Visible = True
.Text = .Index
ElseIf Text1.Count = 9 Then
.Move Text1(1).Left, Text1(Text1.UBound - 1).Top + .Height - 20
.Visible = True
.Text = .Index
ElseIf Text1.Count > 9 And Text1.Count <= 15 Then
.Move Text1(Text1.UBound - 1).Left + Text1(Text1.UBound - 1).Width, Text1(Text1.UBound - 1).Top
.Visible = True
.Text = .Index
ElseIf Text1.Count = 16 Then
.Move Text1(8).Left, Text1(Text1.UBound - 1).Top + .Height - 20
.Visible = True
.Text = .Index
ElseIf Text1.Count > 16 And Text1.Count <= 22 Then
.Move Text1(Text1.UBound - 1).Left + Text1(Text1.UBound - 1).Width, Text1(Text1.UBound - 1).Top
.Visible = True
.Text = .Index
ElseIf Text1.Count = 23 Then
.Move Text1(15).Left, Text1(Text1.UBound - 1).Top + .Height - 20
.Visible = True
.Text = .Index
ElseIf Text1.Count > 23 And Text1.Count <= 29 Then
.Move Text1(Text1.UBound - 1).Left + Text1(Text1.UBound - 1).Width, Text1(Text1.UBound - 1).Top
.Visible = True
.Text = .Index
ElseIf Text1.Count = 30 Then
.Move Text1(15).Left, Text1(Text1.UBound - 1).Top + .Height - 20
.Visible = True
.Text = .Index
ElseIf Text1.Count > 30 And Text1.Count <= 36 Then
.Move Text1(Text1.UBound - 1).Left + Text1(Text1.UBound - 1).Width, Text1(Text1.UBound - 1).Top
.Visible = True
.Text = .Index
ElseIf Text1.Count = 37 Then
.Move Text1(15).Left, Text1(Text1.UBound - 1).Top + .Height - 20
.Visible = True
.Text = .Index
ElseIf Text1.Count > 37 And Text1.Count <= 43 Then
.Move Text1(Text1.UBound - 1).Left + Text1(Text1.UBound - 1).Width, Text1(Text1.UBound - 1).Top
.Visible = True
.Text = .Index
End If
End With
Thanks Natdrip
"I'm not a Nerd, I just prefer Dating invisible women"
Edited by - natdrip on 12/12/2003 2:24:51 PM
Hide Blank Rows On Print
I have a worksheet I set up that has 4 rows repeating on each page ( page setup->sheet->rows to repeat)
the document is an accounts receivable ledger so some days its 50 lines long the next it may be 400.
There are also borders on very cell.
How do I set up the document to only print filled in rows? If I take out the borders ins fine as excel works that way by default. But, when the borders are there it just prints the empty rows.
I'm sure there is something I could add to
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
do something to hide rows here
activesheet.printout
do something to show the rows again here
End Sub
Edit by Moderator:
Please post Excel questions, in the Excel forum.
Thank you.
Code To Hide Rows In Excel
Hi
I'm looking to write code which will select a particular column (A0) and hide all rows with a zero cell in this column. Could anyone help with this please?
Code To Hide Rows In Excel
Hi,
I'm looking to write a procedure using vb in Excel to hide all rows where the content of column B is "DELETED". Could anyone help with this please?
Could the rowref property be used to solve this and if so could you give an example? Or could you help me with some code to move to the row below the current row in the spreadsheet, for example (in words):
Do
RowBelowProcedure:
GoTo Next Row
If cell in column B.value = "DELETED" Then
hide row
Else
GoTo RowBelowProcedure
Loop Until column B.value = ""
Thanks.
How To Hide MSHFlexgrid Subset Rows
hi guys,
i am using mshflexgrid in my application. i am using shape command and it works fine. but i want to give it realistic look. the one generated by vb is not like the one in MSAccess. In access you can see the hierarchical flexgrid of a Primary-Secondary tables in a good way. i wanted to give my application also the same feel.
so i used another mshflexgrid which will again retrieve only the child but the height setting of the main flexgrid is not getting accurate because i cannot set the height of the subset to 0.
i'll put my problem in simple words. i want to make my subset rows height to 0.(of band 1 in example). see the picture..
-- Kishore...
Urgent : Is It Possible To Hide The Rows In A Flexgrid?
hi
in my application i am using the Flexgrid. so how can i hide the rows. i need to hide some records depends on the condition.
one thnig i can do is rowheight=0. but in that rows i have pictures, bcoz of that its not possible that chance too.
plz help i need urgent.
thanks
---------------------------------------------
Srinivas.
Hide/Unhide Columns With One Button
Hi,
I have recently written a macro that hides and unhides a specified number of columns, but would like to build an If statement to consolidate the macro into one procedure. Here's what I'm trying to do...
I have data in columns ("A:F") and would like to be able to hide and unhide columns ("B") with the same button. So, if I click the button once, it will hide columns B,C,D. If I click the same button again, it will unhide columns B,C,D. On the third click it will once again hide the three columns.
Any help would be greatly appreciate. Thanks!
Select Sheets And Hide Columns
Hi,
I got a workbook with 46 sheets. I need to select (range) sheet 12 to 46 and hide some columns.
This is what I tried, but I wasn't really lucky with that:
Sub HideRows()
Sheets(Array("12":"46")).Select
Columns("K", "W", "AA", "AC", "AD", "AE", "AH", "AS", "AL").EntireColumn.Hidden = True
End Sub
What am I doing wrong?
Thanks in advance
Resolved-How To Hide Columns With MSflexgird
Hello.
I have no problem with setting the visibility of columns for databoundgrid but I have not yet figured how to hide columns in msflex grid. And also can you use wrap text function with msflex gird. Hm. I use msflexgrid because I want each row to have a specific color determined value of a field in the row. That is not possible with databoundgrid at least in VB6. And with msfelx gird I would like to hide some colums and use wrap text function. Any ideas would be apprichiated and if someone has any ideas on costum controls that would be great. I am looking for something that has both functions of msflexgrid and databoundgrid. A freeware control somewhere?
Hide Columns In Excel Sheet
Hi,
guys, i need help in hiding columns in excel sheet using userform on load. And but when i read data from the excel sheet, it should read from all the columns in excel sheet including from those hidden columns.
If any of you come across, please help me.
Thanks,
Chandra
Sort By Columns And Rows
Hi,
I have an excel file with records from A1 to U20 (cells). This needs to be sorted in alphabetical order from A1 to A20 then B1 to B20...and so on till U20. That is starting from A1 column downwards and then from B1 downwards.....till U1 and downwards.
Can anybody give me a code for doing this, or is there a way out in excel itself.
Thanks,
John.
Organize Rows And Columns
Hi!
This is an example of what I have now
GENDER COUNTRY RELIGION
Male Canada Christian
FIRST_NAME LAST_NAME MIDDLE_NAME DATE_OF_BIRTH ...
John Smith Peter 1/1/1960
Johnny Smithson Pete 1/1/1950
John Smith Peter 1/1/1960
Johnny Smithson Pete 1/1/1950
GENDER COUNTRY RELIGION
Female Canada Christian
FIRST_NAME LAST_NAME MIDDLE_NAME DATE_OF_BIRTH ...
Cindy Jack Antoinette 1/1/1960
Christine Smithson Pete 1/1/1950
GENDER COUNTRY RELIGION
Female France Christian
FIRST_NAME LAST_NAME MIDDLE_NAME DATE_OF_BIRTH ...
Marie Smith Antoinette 1/1/1960
Isabelle Smithson Pete 1/1/1950
What I want to have is
FIRST_NAME LAST_NAME MIDDLE_NAME DATE_OF_BIRTH GENDER COUNTRY RELIGION
John Smith Peter 1/1/1960Male Canada Christian
Johnny Smithson Pete 1/1/1950Male Canada Christian
John Smith Peter 1/1/1960Male Canada Christian
Johnny Smithson Pete 1/1/1950Male Canada Christian
Cindy Jack Antoinette 1/1/1960Female Canada Christian
Christine Smithson Pete 1/1/1950Female Canada Christian
Marie Smith Antoinette 1/1/1960Female France Christian
Isabelle Smithson Pete 1/1/1950Female France Christian
I would like to find a way to convert from the previous to this new form (all columns next to each other),
I cannot Copy/Paste all rows because I have sooo many rows so I need an automated way!
I appreciate any suggestions...
Many thanks in advance!
Selection Rows And Columns
When a block is selected in excel, how can we get the first and last row and the first and last column in the selected block?
Hidden Rows And Columns
I am having some trouble unhiding rows and columns. I select them and press unhide.. however i just doesnt do it. Any other methods? thanks!
Display Rows As Columns
:SQL Server 2000
I have two tables: tblName, tblValue
tblName: contains basic information about a particular row of a record like CreateDate, ModifyDate etc.. [tblName.NameID, tblName.CreateDate, tblName.ModifyDate etc...]
tblValue: contains field type and the value for that particular field. [tblName.NameID,tblValue.FieldType, tblValue.Value etc..)
My question is how can I write a sql statement that will list all the rows from tblValue fro each row in tblName and display the value from tblValue as Colums instead of Rows:
tblName & tblValue
--------------------------------------------------
RowID CreateDate Value1 Value2 Value3 .....
Note: tblValue (currently) have 32 rows for a particular record.
Thanks in advance for your help.
-EZfriend
Distinct Rows But All Columns?
Is there a way to make a SQL statement that will only return distinct rows but ALL columns?
I have a table somehow like this:
ORDER CUSTOMER ORDER DATE
1 1010 05/04/2005
1 1010 05/04/2005
2 1011 05/09/2005
3 1010 05/10/2005
I want to filter maybe by order number and put the results in a listview control like this:
ORDER CUSTOMER ORDER DATE
1 1010 05/04/2005
2 1011 05/09/2005
3 1010 05/10/2005
I guess I'm thinking of a query like:
"SELECT ALL customer, orderdate DISTINCT order FROM table..." but this of course is wrong. Any help will be appreciated.
Searching Through Rows And Columns
Hi, I am using VB6 and am trying to animate Excel using it. I
have a sheet named Night 1. I would like to search all the
values in that sheet (by only searching one column from the
beginning cell to the last cell with data) and copy out the
values that I want from the Night 1 sheet and copy it in another
sheet. In other words, I have a case where 'If a certain cell
contains values 100 or 101 then copy that entire row into
another sheet. What code can I use to perform this?
Displaying Rows As Columns
i want to displays some rows of my database table as columns.
I have a table with the following definition:
itemId
maxValue
minValue
unit
now the data in the table is like this ::
ItemID MaxValue minValue Unit
-----------------------------------------------
001 23 22 Inch
001 2 1 mm
002 33 31 Inch
002 3 2 mm
And so on …
Now problem is k I have to generate a report in following form::
Item ID max(Inch) min(inch) max)mm) Min(mm)
----------------------------------------------------------
001 23 2 22 1
002 33 3 31 2
And so on …
i m using data report and data environment .. is this possible ?
help needed urgently plz
Swapping Rows And Columns
Ok we're working in excel 2000.
i have a sheet with a list of reports (row) and users (column) showing how many times they have run each report. There are about 75 reports and 100 users.
User1 User2 User3
Report1 3 2 1
Report2 1 1 1
Report3 2 2 3
What I want on a separate sheet is a list of the uesrs who have run the report and each report name. as shown below
Report1 Report2 Report3
User1 User1 User3
User2 User3
User3
Either macros or formulas is fine by me.
Cheers
Edited by - beccong on 5/9/2005 9:21:45 PM
Transposing Rows To Columns Using VB
Hi,
My problem is as follows:
Essentially an SQL query will pull up single value rows (approx:980) from the DB.
I need to convert this single value rows into rows having 14 columns each.
In summary the SQL will return 980 values as individual rows.
These need to be formatted into 70 rows of 14 values each.
the VB documentation does not have any ideas.
help appreciated. thanks in advance
How Do I Hide/unhide Columns In A List View?
I want the user to choose the columns for a specified output though all the columns will be filled
with data , i want to display only those columns which the user selects.Currently i make the width
of the hidden column as - 0 but that the user can just keep his mouse on the header and drag to
see the hidden column. How do I actually hide it???
MSHFGrod Rows And Columns Wierd
I am doing Ok with my mshfGrid project apart from when the user clicks on a row (I am allowing selection by row only) my code does the following:
With mshfComp
If .Mouserow = 0 then Exit Sub ' don't want top row clicked
' other code here
End If
End With
I am finding that when I debug my code the .Mouserow is 1 but I still Exit the Sub at that point, but it seems to happen randomly - any ideas?
The result is that I'm not processing the selections I should be - but only sometimes.
DBGRID - How To Create Rows And Columns
I am using VB 5.0. I'd like to define a certain number of rows and columns at design time with a DBGRID control. I don't know how, and don't seem to be able to identify any properties that permit one to directly access this at design time.
Is this a run time only property?
Either way, I'd appreciate any input on how to accomplish this.
Thanks!
Macro Suggestions (Columns To Rows)
I'm kinda stuck on this one. Could someone give some suggestions. Maybe its something I can do without a macro..donno.
I have a spreadsheet like this (I'm using products and stores in this example, but its not what I'm really using it for ) I'm using the '-' to signify different columns.
Hammer - Target - 35.99 - Walmart - 34.99 - Home Depot - 35.99
Nails - Target - 2.99 - Walmart - 2.00 - Home Depot - 2.50
Shorts - Target - 12.99 - Walmart - 10.99
I used shorts to show that some of the columns won't have all three 'stores'.
I want it to go on seperate rows now though. So, I want it to turn out like this:
Hammer - Target - 35.99
Walmart - 34.99
Home Depot - 35.99
Nails - Target - 2.99
Walmart - 2.00
Home Depot - 2.50
Shorts - Target - 12.99
Walmart - 10.99
**the products would be in one column, the stores would be in another, and the prices would be in the last **
I'd do it manually if it were shorter, but times these 3 columns by 2400 rows and it makes for a long day.
Thanks in advance.
Linking Controls In VB To Columns && Rows
Hey, im a beginner at VB as ye might see soon! I no the basic ideas and coding. Im doing a program for a company on production and need a little help from those in the no how!!
Im using Visual Basic 6.0 from excel.
Right, firstly how do i link a command button in the VBA to a graph created in excel, so that when the user clicks the command button they are taken to the excel page with the graph.
Also i want to link a textbox (date) to a column and row so that when the user inputs a date the dater column and specific row is selected in excel.
Thanks for any help in advance. Sorry if im not testing ye with complicated questions!!
james
|
TRACKER
