Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










VBA For Visible Range


I'm looking for the row and column number, or number of rows and columns, that are visible in activewindow. Activewindow.visiblerange.row, .column (and .Scrollrow, .Scrollcolumn) are nice but

I prefer not to perform division on pixel height; I'm looking for some count or rowcount but I haven't been able to locate the object.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Resize Visible Rows Based Only On Visible Columns Text
Need to correct code to resize all visible rows on a sheet based only on the text in the visible columns. I have tried the below code but when it resizes it is using the largest amount of text in the rows including that in the hidden columns.

View Replies!   View Related
Read Only Visible Columns From A Range
I do hav a sheet in which i do hav some column;s n data

Worktype
Metrics
April
May
June

in work type few rows are hidden which i don't need what i need is i want to populate only comman metrics in other range named metrics i had build a macro for this and that's working fine but the problem is it also reads metrics from hidden rows too. i had tried a loop in which i had placed as hidden column property condition.

View Replies!   View Related
Next Visible Cell In AutoFilter Range
I am looking for a code which can jump to a next cell from the activecell. I use the code

Activecell.offset[rowno][columnno]

This would take me to the next cell. However, this is a problem when the filter is on. I am not able to go to the next visible cell. Suppose if the row increase is 1, then cell selection goes to the hidden cell. I need to bypass the hidden cell and go to the next cell. Can anybody provide a code which does it? I tried searching the forum but could not get anything closer to this as I need something which works with offset and not cells.row.visible...etc.,

View Replies!   View Related
1st Visible Cell In Filtered Range
I'm using a named range called "VFILTER". This range is my filter range. Once the filter does it's thing, I want to set the first visible cell in column a to a variable...I can't get this right for the life of me!

With Range("VFILTER")
.AutoFilter
.AutoFilter field:=25, Criteria1:="DELETE"
.Offset(1, 0).Resize(. CurrentRegion.Rows.Count - 4, 1).SpecialCells(xlCellTypeVisible). _
EntireRow.ClearContents
.AutoFilter

JPhelper.ClearContents
JPhelper = "=IF(ISNA(MATCH(B5,J46DATA,0)),FALSE,TRUE)"

.AutoFilter
.AutoFilter field:=25, Criteria1:="TRUE"
.Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 4, 9).SpecialCells(xlCellTypeVisible).ClearContents
'set the first visible cell in column a to variable
Set OutRange = .Offset(1).Resize(.CurrentRegion.Rows.Count - 4, 1).SpecialCells(xlCellTypeVisible)
End With

View Replies!   View Related
Copy Paste The Visible Range After Autofilter With VBA
In between a long VBA macro, I need to copy the result of autofiltering i.e. the visible cells / rows only, to paste on an other sheet.

If I do this manualy it works but if I record this on a macro, it records the absolute cells range I pick, when in fact the result of the filtering is different every time.

View Replies!   View Related
1st Visible Cell Below Heading Of AutoFiltered Range
Im trying to create a userform that will enable the user to select a record from a listbox and then to edit the info for that record by changing the contents of text boxes which are set to display the current info. What I have seems to work intermittently. I basically use autofilter to find the selected record on the worksheet, then I set the value of each cell in the row to that of the appropriate textboxes. ListBox2.Column(4) contains the unique id for the selected record.

Private Sub saveclient_Click()
Dim WS As Worksheet
Dim newrng As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set WS = Sheets("Clients") '<<< Change
Sheets("Clients").Select
WS.AutoFilterMode = False
Set newrng = WS.Range("A1:e" & Rows.Count)
MsgBox Me.ListBox2.Column(4)......................

View Replies!   View Related
Add Text To Visible Cells In Column Of Filtered Range
Need macro that changes the text in a particular field. Previously, the column to change was column 46 (AT), but now the column is column 1 (A). If I adjust the last line and run the macro, I get this error message: "Compile error: Invalid or unqualified reference".

. AutoFilter Field:=1, Criteria1:="To be capped" 'TYPE
. AutoFilter Field:=29, Criteria1:="OPEN" 'STATUS
.columns(1).Offset(1,0).Resize(rng.Rows.Count - 1,1).SpecialCells(xlCellTypeVisible).Value = "Capitalised"

View Replies!   View Related
Countvisible / Max - Visible
I have all in place to use formula countvisible, does someone know the formula for showing the maximum number out of those visible / filtered.

View Replies!   View Related
Sum Visible Cells Only
I have a need to sum only the visible cells in a row. Certain columns are collapsed depending on the month and there is a YTD colum that I only want to pick up the expanded or visible cells. I found a custom function listed below at Microsoft's website but it only allows for a single range to be entered and I need to pick 12 individual cells, for example A1, C1, E1, etc. There is other information in the cells between and that is why the range won't work. Is there a better way to do this or can this function be modified to allow me to do this?

Function Sum_Visible_Cells(Cells_To_Sum As Object)
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function

View Replies!   View Related
Textbox Visible
I have created a group of textboxes from the Control Toolbox used to enter data into a hidden worksheet. I used the Keydown Event to Run a procedure and hide the textboxes. After setting the Visible Property of each control to false, the image of the last active textbox remains on the screen until the worksheet is scrolled. The image then disappears.


View Replies!   View Related
Sum Of Visible Cells Only
I have a large spreadsheet that I am using multiple drop downs in to sort for
different scenarios. I do not want to use the general data subtotal command
but each time I sort I want to get a total for the visible cells -

View Replies!   View Related
Sum Only Visible Cells
I have a table of data with a filter on the top and a sum total at the bottom. Whenever i filter the list the total at the bottom doesnt change. how do i change it so the sum only calculates the visible cells?

View Replies!   View Related
Sum Only Visible Rows
I have this sheet:

A B
1
2 1 1
3 2 2
4 3 3
5 2 4
6 1 5
7 2 6
8 3 7
9 1 8
10 1 9

SUM(B2:B10) gives 45

Now I add a filter so all the rows with A = 1 will be hidden. That gives me:

A B
1
3 2 2
4 3 3
5 2 4
7 2 6
8 3 7

But SUM(B2:B10) still gives 45. But it should be 22.

View Replies!   View Related
Only Visible On Start Up
I have a workbook that is set to show a form on startup and then the code tells excel to be visible = false. How ever i have disabled the close button on my form therefore i have stuffed my program up and cant edit it at all.

View Replies!   View Related
Calendar Not Visible
I have a userform which has a calendar in it (created in another userform).

When a user opens the userform, the calendar doesn't show up. But it works fine in my system.

I have already copied the MSCAL.OCX file in the user's system. But still the calendar doesn't show up. What other file do I need to give the user so that the calendar works.

View Replies!   View Related
Sheet Only Visible In VBE
In my VBA Project Editor Window I have a Sheet17 without the normal brackets and a sheet name. Also I do not think there is a sheet 17 in the workbook.

put differntly

I have
Sheet1 (InfoOptions)
Sheet2 (Something)
Where the stuff between brackets the name of the sheet in Excel is.
Also in the " properties" window, if I click on the above sheets it tells me the name is Sheetx etc.

I have a strange sheet that only dislays this

Sheet17

-No brackets AND in the properties window it gives
ThisWorkbook

How do I fix this. I am more than willing to send the workbook via email. I can also, if I may, insert a screenshot of this.

I have already tested - this is not a hidden sheet.

View Replies!   View Related
Last Visible Row With Autofilter
I have an autofiltered sheet, on which I need to find the last visible row (all columns have the same # of rows in use). I am trying to use:

View Replies!   View Related
Buttons Not Visible
I have created a great little macro to handle a large amount of data on my spreadsheet that I've been using for several months. It's a shared sheet that is used by many people, but the macro is protected...That being said, something strange has happened that none of my buttons for activating the macro are no longer visible. I can't even create new ones. I get a box outline with a little red X in the upper LH corner. I've verified that the button is in fact set to be visible and set up properly for colors, etc.

I'm not sure what's happened since it happened in a matter of hours...did something get "deactivated" that I need to re-instate?

View Replies!   View Related
No Name Visible In Sheet
When I open Excel sheet it doesnt say the name of the file anymore, usually seen above the menubar. For example when I open a file called book.xls it usually says the name in the blue bar on the top left sideof the menu bar, but not anymore. Im sure there is a simple way to restore this but cant seem to find it.

more details:

1. I open blank excel sheet.
2. Within Excel I click on File>Open and browse to the xls file i want to open.
3. I open file, no problem there, just on top of the blue bar it should say the name of the file and it doesnt.
4. This is the case for ALL excel files I open.
5. Opening a file directly by doubleclicking on it is the same problem.

View Replies!   View Related
AutoFit Visible Columns Only
I have a worksheet that has several hidden columns and wish to have the EntireRow.Autofit apply to all rows in the current region based on visible columns. After using the WrapText method on all visible cells, one of the hidden columns with a lot of characters causes the entire row to be much larger than desired. Is there a way to use EntireRow.Autofit to only expand the height of each row in the current region as necessary based only on the visible cell contents in that row?

Set AllCells = ActiveCell.CurrentRegion
ActiveCell.CurrentRegion.Select
With AllCells
.Font.Size = 6
.Font.Bold = False
.WrapText = True
.EntireRow.AutoFit = True
End With

View Replies!   View Related
Sum Visible Cells With Criteria
I have a worksheet with 26 columns of financial data. ACTUAL and BUDGET for each month and year totals for ACTUAL and BUDGET. I've provided the user with the ability to hide months they don't need to see during their current session. For example, if they are working on May, they may choose to hide June through December.

A user has requested Year To Date totals for ACTUAL and BUDGET. I thought this would be relatively simple. Just add two more columns and add up the visible cells.

I got this function from the Microsoft website and it works if I don't add criteria:

View Replies!   View Related
Text In Cells Not Visible
I created a spreadsheet a while ago filled with data. It's password protected so nobody but me can edit it. But when I open it, I see only gridlines and no data. If I click on a cell I can see the contents up in the Formula Bar, but the sheet itself is blank. I've sent it via attachment to other people and they are able to view it, so it seems my Excel settings are doing something to me. I've tried so many things, but can't seem to work it out.

The text color is NOT white, the background is not the same color as text, etc. I dont know what else to try!!

View Replies!   View Related
Lock A Worksheet Tab So It Is Always Visible
I need to lock a worksheet tab (Main menu) in place so that it is always visible. There are multiple tabs that are all linked to the main menu by hyperlink. When work is finished in a worksheet I want the main menu tab to be visible and usable rather than using the scroll functions to get back to the main menu.

View Replies!   View Related
Copy Visible Cells
I'm filtering some data and would like to copy the visible cells. When using the code below, it copies all but four rows...is there a row maximum limit it can copy?

Rows("31:31").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:="FALSE"
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy

View Replies!   View Related
SumIF Only The Visible Using Vba Code
I was looking at the message board about what i want but i didnt find it as a whole
What i mean is, that i found the sum of the visible cell only or the sum of a condition
Anyway what i want is:

I hide some rows using a vba code and i want to sum what is left under condition

I have 5 columns , the 2 have the interest.

The column e5:e5000 have values

The column f5:f5000 have specific text --> ("On","Off","Other")

I want ,using VBA, to know, its time i run my the code, the sum of the visble cells under the "On","Off","Other" condition in 3 cells
eg. E1 will have the sum of the "On"
F1 will have the sum of the "Off"
G1 will have the sum of the "Other"

View Replies!   View Related
Formula For Visible Cells Only
How do I modify the following formula to work for visible cells only:

=IF(J2=J1,K1+1,1)

For your information the formula is filled through K2:K2122


View Replies!   View Related
How To Copy Only Visible Rows
I have a spreadsheet that has hidden rows, I want to somehow either delete the unvisible rows or to be able to copy the visible rows to another sheet?

View Replies!   View Related
Hiding Visible Objects
I have a code which i use to grab the highest price and the lowest price from various sheets in column D.

There are 19 sheets altogether and the code basically looks at the relevant sheet based on the value in another cell.

Example - if you type value "EUR/USD" then the highest price and the lowest price are taken from the sheet named "EURUSD".

Now - HOW can i HIDE all those 19 sheets without getting the visible object error?

View Replies!   View Related
Selecting Visible Cells
someone the VBA code which only selects visible cells in a sheet.
i.e. if for example, range("a1:d5") contained data BUT

range("a6:d100") contained formulae but NO data

the macro would only highlight range("a1:d5")

However, when say range("a6:d10") contained data, then the macro would highlight range("a1:d10")

and so on.

I want the mcro to do this without me having to manually change the ranges when running the macro.

View Replies!   View Related
Start Colon To Be Visible
I downloaded some data from an extarnal source to excel... the texts I get appear as 'TEXT' (mind the colons at start and end of TEXT).

However, in some of the cells the Start Colon is visible and in some cells it isn't (although it's there and can be viewed in the formula bar).

I want the start colon to be visible.

View Replies!   View Related
VBA Selecting A Visible Comment
I'm developing a little way to be able to modify comments without having to use the mouse (normally I need to right click the ActiveCell in order to choose "Show/Hide comments"). Below is 2 simple lines of code for unhiding the comment of the ActiveCell and then selecting it. What I can't figure out how to do is actually get the cursor to be inside the comment so that I can start modifying text or entering additional text.

Sub comment_unhide()
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Shape.Select True
End Sub


View Replies!   View Related
Using SUMIFS On Visible Lines Only
I have a large table, lots of rows and lots of columns that has material (Sand, stone, etc.) batched information. I wanted to be able to allow the user to use filters to select which data they want to view, then be able to review some summary information (totals by hour of the day, totals by material, etc.) on other sheets.

I had it working really well and fast using DSUM and/or SUBTOTAL, but these functions don't exclude non-visible (Filtered) lines in a list.

Using some previous posts I saw on this site, I was able to come up with something that works, but it is extremely slow.

What I need to do is to take a list of records on a sheet that contain up to 30 material columns of information (Target and Actual batch weight amounts.) I need to group each of these lines by material into a relative time bucket (12:00 AM to 12:59 AM, 1:00 AM to 1:59 AM, etc.) The user can of course select with Time Bucket they want to view

I am using the sumifs function with two critieria ( i.e. Time >=12:00 and Time < 1:00 ) to get the hourly buckets.

You can also see from this code that I have to count the number of lines for the data and each criteria value because I could not figure out how to set the range of "B" & "C" without counting. Since I am using a filter, I know that the ranges (from a row perspective) of A,B & C are the same, just the relative columns are different. I tried to use the offset function, (i.e. Range(B) = Range(A).Offset(-1,0).Select or B = A.offset(-1,0).Select but they failed for some reason, and no error messages either. I think I somehow turned the erroring off.

Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim Cell As Range
'Application.Volatile
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Else

View Replies!   View Related
Hpagebreaks On Visible Rows
to put hpagebreaks when one or more checkboxes are checked. For example when CheckBoxChange and CheckBoxDisco are checked, hpagebreaks are put below the Change data and Disco data.

The checkboxes also hide rows of data when checked.

View Replies!   View Related
Label Visible By Value In TextBox
I have the following code written to check calculations. Now I would like to make one or another Label Visible depending on the value this code returns.

If TextBox57 = 0 then I want Label 7 to visible but if it doesn't I need Label 8 to be visible. I am starting both Labels hidden.

View Replies!   View Related
Cell Border Non-visible?
I have seen spreadsheet where the user has made the outline of the cell border non-visible. This seems like a fairly simple task, but I can't seem to find out how you format this type of border.

View Replies!   View Related
Autofilter - Sum Visible Cells
I've got an AUTOFILTER and would like to add only the visible cells in a particular column (column E). As the user changes the filter, the total would change - but I'm not sure where to even start with this one. I've attached a sample file.

View Replies!   View Related
CountIF Visible Cells Only
I've setup a worksheet whereby rows can be Autofiltered between a start and finish date via a Userform which (after much mucking about with UK/US dates) seems to be working nicely now. With the results of the Autofilter, i wish to count certain entries (in this case entries that are in column F) in the remaining visible rows.

The Workbook is about 'Cases' worked, and column F relates to how the 'Case' is cleared (Call, Letter, Referral). Effectively I want to be able to count 'Call', 'Letter' and 'Referral' entries once the Autofilter has been applied.

View Replies!   View Related
Numbers In Visible Cells.
I had a situation in which i need to number the cells which start with the Text "SB" in column B. To elaborate,i want to give a serial number for all the transactions starting with "SB". For this i filtered the data >> Custom>>Begins with>>"SB"(Quotes Ommitted). I want to increment the cells with the serial numbers 1,2,3 and so on only those which contain SB. I tried using it with the ROW Function.

View Replies!   View Related
Counting Only The Visible Rows
How can I count the no. of rows of a selection which has some hidden rows...I want to count only the visible rows

View Replies!   View Related
Set PivotItem Visible To True
I recorded the following in a macro.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 8/16/2006 by BPETTIS
'

'
With ActiveSheet. PivotTables("PivotTable1").PivotFields("CT Comp")
.PivotItems("0.9").Visible = True
.PivotItems("1.3").Visible = True
.PivotItems("1.4").Visible = True
.PivotItems("1.5").Visible = True
.PivotItems("1.6").Visible = True
.PivotItems("1.7").Visible = True
.PivotItems("1.8").Visible = True
.PivotItems("1.9").Visible = True
.
It was recorded when in the indcated PivotField is selected the "ShowAll" option.

However, when I try to run this macro I get the following error

"Run-time error '1004':
Unable to set the Visible property of the PivotItem class

Why am I not able to execute a recorded macro. Also how do I set a Pivot Item's visiblity to "TRUE"

View Replies!   View Related
Counting Visible Rows
I have two macros that hide or unhide rows. Now I need a way to count only the non-hidden rows. The total needs to be visible in each visible cell of a single column (presently column AW).

Example - if rows 2 and 3 are hidden using the "Hide Row" macro, the following would happen;

Row 1 is visible, so it is numbered "1" in AW1.
Row 2 is not visible, so it either has no number or freezes at "1" in AW2.
Row 3 is not visible, so it also has no number or freezes at "1" in cell AW3.
Row 4 is visible, so it is numbered "2" in AW4.
And so on.

It doesn't really matter what is in the AW cell in hidden rows because I am not totalling rows. What I am doing is using it to define a print area. If a page has to be 70 rows long, another macro uses column AW to work out where to put page breaks (after row 70, 140, 210 and so on). If twenty rows were hidden on page 1, without the above solution, the printed page would only be 50 rows long. A solution to the above problem will always make sure only visible rows are counted when calculating where to put page breaks.

I am using Excel 2000.

View Replies!   View Related
Make Workbook Visible
I have a workbook that was opened with a Word macro but was mistakenly not made visible. I didnot realize the workbook was open because I didn't see it. I tried to open it manually and found that it was locked for editing because it was already open due to the Word macro opening it. How do I close it?

View Replies!   View Related
Scrollbars Not Visible In Textbox
I have set the "Scrollbars" attribute to a text box within a form to "2 -
frmScrollBarsVertical" -- and i want the TextBox to be Disabled (so wrote the code as TextBox.Enabled = False) the issue is that when the form is initiated, the
scroll bar doesn't appear and i am missing the Text.

View Replies!   View Related
Average Visible Cells
I've got the following function working, until I paste it into a cell that is referencing a range with all zeros.

Function Ave_VisibleUnits(Cells_To_Ave As Object, MyStep As Integer)
Dim i As Integer
Dim vCount As Long
Dim vTotal As Double
Calculate
Application.Volatile
vTotal = 0
vCount = 0
For i = 1 To Cells_To_Ave.Cells.Count Step MyStep
If Not Cells_To_Ave(i).Rows.Hidden Then
If Not Cells_To_Ave(i).Columns.Hidden Then
vCount = vCount + 1
If Not Cells_To_Ave(i).Value = 0 Then
vTotal = vTotal + Cells_To_Ave(i).Value '(It dies here)
End If
End If
End If
Next
vTotal = vTotal / vCount
Ave_VisibleUnits = vTotal
Calculate
End Function

View Replies!   View Related
Print Visible Sheets Only
I use templates (.xlt) to generate reports for different organizations. In some cases certain pages in the reports are hidden.
When printing I get blank pages where pages are hidden. How do I not get these pages printed at all?
I do use page breaks to format the reoprts.

View Replies!   View Related
Keep Data Summary Always Visible
I have a spreadsheet, whereby I have a list of items that grows regularly, but also have a brief summary of items, however I would like the summary to float, so that it always appears on screen, and I don't have to hunt for it on the spreadsheet.

View Replies!   View Related
Paste To Visible Cells Only
Is there any method to paste a range of cells on visible cells only (ignore hidden cells).

View Replies!   View Related
Odd Error - Functions Remaining Visible!
I've got a very simple formula in Column P:-

=IF(O1="No","N/A",IF(O1="","","Needed"))

If I click on the cell to review this, it vanishes once I click out and still works. If, however, I click in the cell to edit it and then hit "Enter" the formula stays visible and refuses to work!

I've then tried it on other places where I have a formula on the same worksheet and the same thing happens.... And yet on other columns, it doesn't.

View Replies!   View Related
Sum According To Auto Filter Non Visible Cells
I tried using SUBTOTATL but no luck.

As always, I prefer without helper column(s) and/or VBA - but If not - I will, probably, have to compromise.

View Replies!   View Related
Visible Row Number In Autofiltered List
I have a list of numbered items and what I would like to do is display that items position in the list should an autofilter be applied

For example, with no filter applied the list could look like this:-

Col A Col B Col C
1 1 Armadillo
2 2 Platypus
3 3 One-legged Schornztickler
4 4 Armadillo

And when the autofilter is applied to Col C with a criteria of Armadillo the results should display thus:-

Col A Col B Col C
1 1 Armadillo
4 2 Armadillo

I'd really like a non VBA solution, but as I can't work one out myself I guess that's not an option?

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved