Select The Top Right Cell In A Non-contiguous Range
Is it possible, through VBA, to select the top right cell in a non-contiguous range? For example, I have defined A1, A3, and A5 as a range. How could I tell Excel to select A5 from within that range?
I am using Excel 2007.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Select Non Contiguous Blank Rows Via Array
I have a problem with the following code. I want to select multiple rows in a very huge spreadsheet. But, when I use this code, I receive an error: Runtime error 1004, Method ' Range' of Object '_global' failed. What is wrong? Or maybe there is other better way to select automatically empty rows in a spreadsheet? Public Sub delhol() ' ' delhol Macro ' ' Keyboard Shortcut: Ctrl+d ' Dim k As Variant Dim rr As String k = Array(34, 35, 38, 39, 40, 77, 133, 182, 207, 209, 225, 226, 295, 299, 300, 338, 394, 437, 468, 470, 480, 481, 560, _ 591, 599, 655, 712, 729, 746, 755, 756, 852, 860, 962, 990, 1005, 1006, 1077, 1081, 1082, 1083, 1114, 1176, _ 1217, 1252, 1260, 1261, 1338, 1342, 1343, 1344, 1375, 1381, 1437,
View Replies!
View Related
Select Non Contiguous Ranges Via Macro, Using Variables
trying to select multiple ranges of data at once using variables as my selection range criteria. I.e., I want something similar to: Range("A10:A30,B10:B30,E10:E30").Select But would like to be able to perform the same selection using variables. StartVar = 10, EndVar = 30 I'm sure it's just a matter of syntax, but I can't seem to get it right.
View Replies!
View Related
VBA To Identify Top Left Cell Of A Selected Range
if a user has selected a range, how do you, in VBA, identify the: 1. Top left cell 2. Bottom left cell 3. Top right cell 4. Bottom right cell For example if user has selected the range B5:M30, then we would want to identify in the macro: 1. Top left cell = B5 2. Bottom left cell = B30 3. Top right cell = M5 4. Bottom right cell = M30
View Replies!
View Related
Resizing Non-contiguous Range
Is it possible to re-size a non-contiguous range? I'm guessing "no", and a Google trawl hasn't provided me with anything. Here's what I'm trying to do: dim mult_rng as range dim rng2 as range set mult_rng = range("A1:B1,D1:E1") set rng2 = mult_rng.resize(rowsize:=10) That is, my original range is comprised of non-contiguous cells in the same row. When re-sizing, I'm trying to increase the number of rows, but leave the columns the same. Like I said, my gut instinct says that this isn't possible using the 'resize' property. Can anyone think of another method to use? My constraints are that the original mult_rng isn't always the same (and isn't always non-contiguous).
View Replies!
View Related
Grouping Contiguous List Into Non-Contiguous Groups
I would like to be able to count the amount of entries in column C and depending on the amount group them in either groups of 3 or 4, all names would be unique...so if there are 14 names in the list they would need to be grouped in to two groups of 4 and two groups of 3, if there were 19 then 4 groups of 4 and 1 group of 3 etc to a maximum 50 people, the results could appearon a seperate worksheet say pasted on to the worksheet starting with the groups of 3 (so paste a group of 3 then skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to allow seperation and manual entry of extra data). There will never be groups of 5 or more and never less than 3
View Replies!
View Related
Define Non Contiguous/Continuous Range
I am building a macro to conditionally edit cells that meet certain criteria within the current selection: Dim FinalStr As String If CheckBox3.Value = True Then For Each cell In Selection If cell.Font.ColorIndex = Range2.Font.ColorIndex Then FinalStr = FinalStr & "," & StrConv(cell.Address, 1) End If Next End If FinalStr = Right(FinalStr, Len(FinalStr) - 1) 'To remove extra "," at the beginning What I am getting from this macro is a string like "$I$27,$J$27,$E$28,$F$28" that I use later to edit that range, for instance Range("$I$27,$J$27,$E$28,$F$28").ClearContents It worked fine, however, I found later that XL cannot handle more than 20-30 individual cell addresses. The error msg I am getting is: " Method 'Range' of object '_Global' failed " Note that it won't be very practical to pick the cells and edit them within the same loop since there are several criteria times several edit options.
View Replies!
View Related
Macro: Count Non-Contiguous Range
I'd like to count non blank cells in 16 separate ranges (each range consists of 6 consecutive cells). The first range in the series is C9:H9. 3 cells are then skipped and the process is repeated with the next range, ie L9:Q9 then skip 3. The last range in the series of 16 is therefore EK9:EP9. If the value of non blank cells = 2 in any of these 16 ranges then copy the contents of this range to C24:D24 in sheet " Record Form Games 3583". If C24:D24 is not empty copy these values to C25:C26. If the value of non blank cells = 6 in any of these 16 ranges then copy the contents of this range to C22:H22 in sheet "Record Form Games 3583". If C22 is not empty copy these values to C23:H23. The ranges colored yellow in the example workbook are the ones that need to be copied to the sheet Record Form Games 3583.
View Replies!
View Related
SQL Queries To Non-contiguous Range
I want data to columns A, C and E, while I have important data in columns B and D. Should I make three queries? Should I make query and save the results to temporary place and then move all the results to those columns A, C and E? Right now the copying happens like this: If Not rsData.EOF Then rngTarget.CopyFromRecordset rsData Else MsgBox "No records returned.", vbCritical End If So should I do 1) One query and move everything in the right places 2) Multiple queries 3) Something else Does this depend a lot about how many results I expect? I'd say likely about 100, maybe less than 1500, never more than 10000
View Replies!
View Related
Assign A Non Contiguous Range To An Array
When I insert a line to select the range on the Worksheet it does select the range named as temp. However, the array does fill but only with the first part of the range eg in this case A7:C44. My aim is to create an array of size point1A( all the rows in the multiple ranges, 3 columns). Example of a Range to be assigned to the Array point1A. temp = "A7:C44,D7:F44,G7:I44,K7:M44,N7:P44,Q7:S44,"
View Replies!
View Related
Number Of Columns In Non-Contiguous Range
Consider the following range (in my case a PivotTable):A1:E7 I create a Named Ranges that corresponds to column 1:3 and 6 from the range above. i.e : A1:C7,E1:E7 I need to find out the number of columns of my range, this is because it can change in the future, and I have an offset formula needing this information. When i type =columns(MyNameRange) it returns #REF! as there are 2 areas in my Named Ranges. I am expecting 4. I have written the a VBA User Defined Function that does the job, but I would like to know if there is no native solution to get this information. Function TotColumns(rng As Range) Dim Cols As Integer For Each Ar In rng.Areas Cols = Cols + Ar.Columns.Count Next TotColumns = Cols End Function
View Replies!
View Related
Dsum With Non-contiguous Range Criteria
Is it possible to use a non-contiguous range for the criteria parameter of the DSUM function? I want to do something like this. =DCOUNTA(wholesale_all,"Assignment Date",D2:D3,G2:G3) This formulas won't work the way I have it typed, but is there a workaround to achieve the same result?
View Replies!
View Related
Select Cell Within A Range
how to select a cell in opposite (i.e right side of a range) while using a macro. My macro selects a certain range based on user input. The active cell is the left most upper cell. I need to move the active cell within the range to the right upper most cell. I tried the short cut key - tab, while recording a macro, but the macro just selcted a that certain cell.
View Replies!
View Related
Manipulating On A Range Of Contiguous Rows Using VBA
I have a contiguous range of rows starting from row 5. ...It starts from row 5 but could end anywhere below. The name of my Sheet is Tester. Now in Tester, columns B,C, and D have numerical values in them. Using VBA, how can put in values in corresponding row of column A. So if there are values from B1:D39 and B40 onwards is empty, then A1 will have value B1*C1*D1, then A2 will have B2*C2*D2 and so on until A39 = B39*C39*D39 **I need to write the code so the whole process is done fastest.The code may be long but the macro needs to run as fast as possible. (Also, I remember using Screen.Updating = False when I wrote similar stuff many yrs ago. Would this be useful here?)
View Replies!
View Related
Hide Blank Rows In Non Contiguous Range
How do you select certain ranges in the same column to filter? The ranges I need are "c36:c50" & "c54:c68" & "c72:c87" & "c91:c155" & "c158:c172" & "c176:c202" all filtering for blanks. I can not filter from c36:c202 because there are blanks in the missing rows and they have to stay.
View Replies!
View Related
Check Non Contiguous Range To See If All Cells Are Filled
Is there a way to prevent a workbook from closing or being submitted until information has been entered into the following cells? B78, B80, B82, B84, B86, B88, B90, B92, B94, B96, B98, B100, B102, B104, and B106?
View Replies!
View Related
Check For Empty Cells In Non Contiguous Range
The application reads in a file, whcih can have various formats. To check which format it's in, I plan to look for certain empty fields/ cells. I can successfully detect a group of empty cells by explicitly testing each one, but when I put them all in a range and test that, the check fails. So far I've reduced the problem to the following example code. Sub check_clear() Range("g1:g8").clear If IsEmpty(Range("a1")) Then Range("g2") = "A1 empty" End If If IsEmpty(Range("b1")) Then Range("g3") = "B1 empty" End If If IsEmpty(Range("c1")) Then Range("g4") = "C1 empty" End If If IsEmpty(Range("d1")) Then Range("g5") = "D1 empty" End If.............. The result is that each individual cell check results in the relevant "XX empty" message. However, the test that the range of multiple cells is empty never produces a result. I'd really like to understand the underlying reason - as well as find out how to perform an isEmpty test on a range. I'm looking more for guidance and insight than a canned solution
View Replies!
View Related
Reference Non-Contiguous Named Range In Function
I have a split named range covering non-contiguous areas, ie: Name: status_web Range: =$F$14:$I$122,$F$700:$I$746,$F$798:$I$830,$F$905:$I$933 This appears to work fine when selecting the name however it returns a #VALUE! error when using it in a formula, eg: = COUNTIF(status_web, "*p*"). Is there any way to get this to work, or do I need a separate named range for each contiguous section? NOTE: Not sure whether this extra fact would through a spanner in the works, but I am referencing this named range from a separate workbook.
View Replies!
View Related
Nth Lowest Number In Non-Contiguous Range
I want to use the Small function on a set of cells that are non-contiguous. But I know that the Small function only works on an array, which cannot contain cell references. Copying the cells to a contiguous range is not an option, as some of the cells I want referenced are generated on the fly in a Sumproduct function. Is there some alternative I could use? I can use the Min function to get the lowest value, as it allows for references, and the Max function to get the greatest, but what about those in between?
View Replies!
View Related
Select Range Based On A Cell Value
I have a worksheet with a block of data. The size of this block changes. I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet. So far I have everything working fine. Now I want to create a button to trigger a macro to sort that block. I can figure out the button and make it trigger a macro. One of the lines in the macro is
View Replies!
View Related
Copy Non-Contiguous Range & Paste To Last Used Row
I am trying to have this loop go through colums of data and compare columns 3 and 4. Where there are differences, parts of the row are copied and pasted in a summary table. My problem is the output to the summary table. How do I control which cells in the row are copied? (I only want to copy columns 1, 3 & 4) How do I control where the copied cells are pasted to? Sub Change() Dim r As Long 'Loop to Compare Values For r = 4 To Cells(Rows.Count, 1).End(xlUp).Row 'Looks For Funds That Upgraded If Cells(r, 4) < Cells(r, 3) Then 'Highlights the row Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 34 'Copies The Applicable Cell Over Into Column 7 Cells(Rows.Count, 7).End(xlUp).Offset(1) = Cells(r, 1) 'Places a note into column 5 Cells(r, 5) = "UPGRADE" End If If Cells(r, 4) > Cells(r, 3) Then Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 36 Cells(Rows.Count, 7).End(xlUp).Offset(1) = Cells(r, 1) Cells(r, 5) = "DOWNGRADE" End If Next r Columns("G:G").EntireColumn.AutoFit End Sub
View Replies!
View Related
Restrict Worksheet Change Event: Non-Contiguous Range
is it possible to have a userform activated as a result of a Worksheet_Change event on more than 2 cells. Currently I have the code below which will activate userforms if either a cell in Column G or a cell in Column T is changed. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("G:G", "T:T")) Is Nothing Then Exit Sub If Intersect(Target, Range("G:G", "T:T")) = "" Then Exit Sub If Not Intersect(Target, Range("G:G")) Is Nothing Then GlngRow = Target.Row Call Module3.ShowList1 End If If Not Intersect(Target, Range("T:T")) Is Nothing Then GlngRow = Target.Row Call Module3.showlist4 End If End Sub What i need to do is activate another userform if a cell in Column AC is changed. I tried this code but got an error "Compile Error: Wrong number of arguments or invalid property assignment", which leads me to believe I can't add any more cells to the argument and it highlights the word Range in this line - If Intersect(Target, Range("G:G", "T:T", "AC:AC")) Is Nothing Then Exit Sub..........
View Replies!
View Related
Intersect In VBA: Select More Than One Cell In The Range
I have a piece of code that changes the format of a cell using an intersect statement on the range in question. However, if I try and select more than one cell in the range then an error message is returned: 'Run Time Error 13': Type mismatch. I know that it is caused by selectin multiple cells - is there a way to error handle this so that, when multiple cells are selected, it ignores the intersect statement?
View Replies!
View Related
Add Items: Multi Column ListBox: Non-Contiguous Range
i have several listbox with two columns in a userform in a sheet i have several columns whith values and i'm trying to put the values from the columns in the respective listbox. down is the code i wrote but is stressing with column command Private Sub UserForm_Initialize() Dim contar, j, i As Double Sheets("Historico").Select contar = 0 For j = 1 To 15 linha = j For i = 3 To 65536
View Replies!
View Related
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result. For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z). The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Replies!
View Related
Select Range Based On Variable Cell Content
Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.
View Replies!
View Related
Select Cell Range Relative To Starting Position
I need to change multiple conditional formats which apply to cell ranges 1 row x 17 columns wide. The specific change I am making is able to be acrried out with the code With Selection.FormatConditions(1).Font .ColorIndex = xlAutomatic End With However, I dont want the user to have to manually select each 17 cell area. I WANT THE USER TO JUST MANUALLY SELECT THE FIRST OF THE 17 CELL AREA. I want to write a macro that will THEN select a row of cells, 17 cells long, from the MANUALLY SELECTED position as at the moment of running the macro. Unfortunately the cells are not always on the same row in each sheet. On one sheet they may be on row 5, on another row 8 and on another, row 15 etc. I was wanting to be able to select a 17 cell range 'relative' to where the cursor is. How do I write the code to select the relative cell range? Is there an answer in an R[1]C[1]:R[1]C[17] sort of code?
View Replies!
View Related
Select Single Cell After Paste Large Range
I would like to select cell A6 after pasting a large range so that when send to user the entire range is not selected. I tried wsDestPage.Range("A6").Select And wsDestPage.[a6].Select I receive Select method of range class failed How can I select a single cell after pasting the large range?
View Replies!
View Related
If Select Sheet, Select Range Statement
I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now. Private Sub Worksheet_Activate() If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then Me.ClearPrevious.Visible = True Else Me.ClearPrevious.Visible = False End If Sheets("MASTER").Select End Sub
View Replies!
View Related
Indexing Top X Highest Of A Range
In the attached sheet, I'm trying to get excel to return the top 5 values in an array, based on the preceding cell. This example is for a Football League, in which the QBs, RBs and WRs all have different salaries. The objective is to return the salaries of the top 5 paid people in each. Don't need to return the name, just the salary amount, so that an average can then be taken to figure future salaries.
View Replies!
View Related
Find Top/bottom 3 Values From A Range
I now have everything set up to do exactly what I want except this last bit. All I want is for the overview page to show the top three referrers for each month. At the moment, it's finding the top and bottom 'scorer' but if more than one has the same score I can only see the first alphabetically.
View Replies!
View Related
Tick Cell Upon Selection. Restrict To Non Contiguous Ranges
I am trying to use excel to score a test. I want to tick a cell to do so. There is an excellente information about Tick Cell Upon Selection, posted here http://www.ozgrid.com/VBA/excel-checkbox-tick-cell.htm. Its range is limited to (A1:A100) Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A1:A100")) Is Nothing Then Target.Font.Name = "Marlett" If Target = vbNullString Then Target = "a" Else Target = vbNullString End If End If End Sub How can I increase the range so it would target C1:C30, E1:E30 ( total of 17 columns)?.
View Replies!
View Related
|
TRACKER
