Select Non Contiguous Blank Rows Via Array
Feb 17, 2008
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 4 Replies
ADVERTISEMENT
May 15, 2008
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 7 Replies
View Related
Mar 30, 2012
I came across this code that selects (and extends) cells between the active cell and the next cell with data.
Is it possible to modify this so that it selects the entire Rows ?
Code:
Sub Test()
If Selection.Select "" Then
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Else
Range(Selection, Selection.End(xlToRight)).Select
End If
End Sub
View 4 Replies
View Related
May 29, 2009
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 9 Replies
View Related
May 4, 2014
i want to delete entire blank row from column C2:C300 i mean if i have data c2:c100 then c101:c300 delete entire blank rows
View 4 Replies
View Related
Jan 21, 2012
I need a code that will copy the format AND formulas of the entire row that I have selected (or activatedwith my cursor) to each and every blank row - until it gets to the last row of data on this spreadsheet.Since any employee could have more than 1 row of data - I am using a code that inserts1 blank row after each NEW employee name.
NOTE: This report is initially sorted by employee name so that each occurrence is grouped together.REPORT SPECIFICS:1) This report reflects typical paryoll information.2) Certain columns have data that is either in text, general, or number ($) format3) The number of columns may vary depending on which PR report is being worked4) For the sake of simplicity - we can assume that the column titles will always be across row 15) Each employee name on this report may repeat several times depending on how many weeks they worked,so the SUM() formula should adjust automatically to capture all the rows of data to add up for each employee
HERE IS MY SPREADSHEET:
texttexttexttexttextformulaformulaformulaDeptDivEmployee NameEE#Period EndHrs WorkedPTOAccrd LiabilityCSDINSEWilma Wilsont4561/7/1280.5$ 100.00 CSDINSEWilma Wilsont4561/16/12121$ 200.00 CSDINNECage Nick2581/7/1281$ 600.00 CSDINNECage Nick2581/16/12245$ 25.00 CSDINNECage Nick2581/23/12323$ 25.00 CSDINWPolly Cracker1781/7/12856$ 60.00 CSDINWPolly Cracker1781/16/12242$ 654.00 ARVIPWPolly Cracker1781/23/12322$ 2.00 ARVIPWPolly Cracker1781/28/12161$ 5.00 ARVIPWPolly Cracker1782/6/1284$ 3.00 CSDINSEDim Sum6871/7/1284$ 65.00 CSDINSEDim Sum6871/16/12126$ 5.00
HERE IS MY CODE TO INSERT 1 ROW
Sub InsertRowAtNewNameONE()Dim LR As Long, i As LongLR = Range("C" & Rows.Count).End(xlUp).RowFor i = LR To 2 Step -1If Range("C" & i).Value Range("C" & i - 1).Value Then Rows(i).InsertNext iEnd Sub
NOTE: See below: I will format and add formulas where I need on the 1st blank row that was createdand I need a code that will copy this particular row to all blank rows to the last.
recordtexttexttexttextcountformulaformulaformulaDeptDivEmployee NameEE#Period EndHrs WorkedPTOAccrd LiabilityCSDINSEWilma Wilsont4561/7/1280.5$ 100.00 CSDINSEWilma Wilsont4561/16/12121$ 200.00 2201.5$ 300.00
View 5 Replies
View Related
Jan 25, 2009
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 6 Replies
View Related
May 7, 2009
I'm trying to select a contiguous range of cells in a column and then iterate over that. I keep getting error "91", object variable not set. I have:
View 5 Replies
View Related
Apr 29, 2008
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 3 Replies
View Related
Aug 2, 2014
I have tried:
Code:
With Sheet1
x = .[b7:i16,b19:i23,b27:i32].Value
But that only loads the first range (.[b7:i16]).
This does work:
Code:
.[b7:i16,b19:i23,b27:i32].Copy .[z1]: x = .[z1:ag21]: .[z1:ag21].Clear
I would rather avoid having to copy/paste, load the array, then clear. Is there a way to load the array directly?
View 2 Replies
View Related
Aug 19, 2009
I recently found this code for selecting a whole column of non continuous cells.
ActiveSheet.Range("a1",ActiveSheet.Range("a65536").End(xlUp)).Select
How can I change the "a1" & "a65536" so it can work and be activecell instead?
View 9 Replies
View Related
Mar 19, 2013
Need code that will search non-contiguous range for first empty cell, paste data into found cell and data into offset cells and end search. If not empty, move to next cell in non-contiguous range. If NO empties are found in entire range, a msgbox.
Non-contiguous range: Range("B2,B32,B62,B92,B122,B152,B182")
Pasted data: 1st range into found empty, 2nd range into range offset of empty.
View 9 Replies
View Related
Nov 15, 2009
I have a workbook that is used for storing test results that relate to people in different groups.
B4, C4, D4 are the surname, first name and group headings. This is what I want to filter by.
Column E is empty
F,G,H,I,J,K are the results.
Column L is empty
M is total of results
What I want to do is set an autofilter from B-L, only have drop downs on B,C,D. But in addition to this I want to have it that the range can dynamically change if another column of results is inserted.
To calculate my formulas in the worksheet I named/defined M4 as 'Total' so that I could then offset from this named cell to determine the range of test result columns, which works well. I just don't know how to do this in vba.
I have this to just get the autofilter running:
With ActiveSheet
.Unprotect ("password")
.AutoFilterMode = False
.Range("B4:L4").AutoFilter
.Protect ("password")
End With
I need the range to be B4: offset of the cell 'total' 0,-1
and I need to loop through from F - offset of the cell 'total' 0,-1 and turn the dropdown off on each of these headers.
View 9 Replies
View Related
Apr 13, 2008
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 9 Replies
View Related
Mar 27, 2014
I am creating a tracking spreadsheet where i can toggle between months (attached). It returns data properly when I select January or February from the yellow highlighted dropdown list. However, when i choose March, it returns 1/0/1900 because no data has been entered for March (table array) on the AAAG tab. How do I write the formula to return a blank cell on the summary sheet when no information has been entered into the table array yet?
View 2 Replies
View Related
Sep 9, 2006
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 8 Replies
View Related
Dec 1, 2012
I'm trying to write a 'ReImport' macro to copy data from one model into another. I'll call these models 'Master' and 'Split'. 'Master' contains about 50 departments' worth of financial data, and the 'Split' files contain a Division's (between 1 and 20 departments') worth of data. Structurally, the 'Split' files are just copies of the Master with the unneeded departments hidden. They are sent to various Divisions for completion, then the ReImport process takes data from the Split files and copies it back into the Master. Every Split File contains the full 50 departments from the Master, and it is only the 'Visible Row' distinction plus a 'ReImport key' that should determine what gets ReImported.
Each 'Split' range has a range name that controls what Departments are hidden or visible, but the range name is not contiguous. In other words, Division A might show Departments 1, 4-8 and 23. (Reordering the Depts is not an option.)
In a perfect world, I want to evaluate ONLY rows contained within the Division range name for the ReImport key. But the non-contiguous aspect of the named range is throwing me off. So in the above example, JUST Departments 1, 4-8 and 23.
My second-best solution would be to search through Departments 1-23, though that would be much slower...
View 5 Replies
View Related
Nov 11, 2009
I am drawing a complete blank. If in a macro I have determined and built a string list of non-contiguous rows that need to be deleted (e.g. rows 3, 5, and 7) from a range of rows named "Prices", what would be the correct syntax?
For example, I know it isn't..
Prices("3:3", "5:5", "7:7").Rows.Delete
because this gives me a "Type Mismatch".
View 9 Replies
View Related
Feb 6, 2013
I have two pieces of code that perform virtually identical functions on different sets of data.Basically, the code deletes rows of data based on a selection in a listbox. One code works perfect, the other has an issue.
The Issue:
When I select a single row for deletion, or multiple contiguous rows, everything works fine. It is when multiple Non-Contiguous rows are selected that the problem occurs. I get a "Delete Method of Range Class" error.
The data for the code that doesn't work is in a defined TABLE, the date for the code that does work isn't. So I assume this is where the problem lies.
I converted the data for the code that did not work from a table to a range, and everything works fine. However, I would really prefer that this data remain a table. There is consistently going to be rows added and deleted, and a table will take those adjustments into account, whereas a named range shrinks each time I delete a row, and doesn't grow as I add rows.Here is the code that works:
VB:
'Delete Selected Offers
Else
For r = 0 To ListBox1.ListCount - 1 [code].....
View 6 Replies
View Related
Apr 10, 2013
However I have a subject at uni that requires me to create a series of regression models, histograms, correlation matrices etc.
For part of the assignment, i have to run 4 regressions (one for men & one for women) with dependent variable as average wages, and independent as bfast 1,2,3 and dinner 1,2,3 (all of which are dummy variables) (0 for male and 1 for female).
The second two regressions are exactly the same, except average wages must be transformed into log which i have already done.
I dont know how to differentiate the regression models into male and female (if possible). We must also include residuals and residual plots.
I keep getting an error that says non-numeric data, the other says input range must be a contiguous reference.
View 1 Replies
View Related
Jul 15, 2008
I can count the blank cells withiin a range using
=COUNTBLANK(C6:AD2506)
But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.
It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.
View 14 Replies
View Related
Aug 29, 2012
Can I do a select case on an array?
Something like?
Code:
Dim Array As Variant
Dim Range as Range
Array = Array(Worksheets("sheet1"), ("sheet2"), ("sheet3"))
Select Case Array
[Code] ........
View 9 Replies
View Related
Sep 13, 2010
I am trying to select specific values within an array.I have two tables, weight table and a factor table. These tables are a lot bigger than the example. I am trying to multiple the weights times the corresponding factors and then sum the result in one cell using arrays.
The third table is the result I am looking for. However, I would like to use some type of index/match or choose to get the result but cannot figure out how to do it. A couple of functions that I tried that did not work are next to it.
The index fails because the index function does not output an array (The result of the index function is just 4% instead of {4%,3%,4%}). The choose fails because the choose function does not select values within an array (ie I would have to put each factor as a separate value (F4,G4,H4) rather than the entire array (F4:H4). There are so many factors that I would like to avoid that.)
Sheet3
ABCDEFGH1 2 3NameWeightFactor DateFactor1Factor2Factor34Name125%Factor3 7/1/20105%3%4%5Name235%Factor2 8/1/20107%2%3%6Name240%Factor3 7 8DateReturn Does not work 97/1/20101.04% 4.00%#VALUE! 108/1/20102.65% 3.00%#VALUE!
[Code] .......
Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
View 3 Replies
View Related
Feb 6, 2007
I have a problem with a listbox on a userform. I have an array that is stored in a name. The array has 2 columns and + 1000 rows. It looks like this:
100 Name1
110 Name2
120 Name3
Etc.
The listbox is filled from the array:
Private Sub UserForm_Initialize()
frmZoeken.lstbx_Gbr.Clear
frmZoeken.lstbx_Gbr.ColumnHeads = False
Dim myArr()
myArr = Evaluate("varRekSchema")
frmZoeken.lstbx_Gbr.List = myArr
End Sub
When the form with the listbox opens, it has to select by default the value in the listbox that corresponds with a value in a cell in the workbook:
Private Sub UserForm_Activate()
'On error resume next
frmZoeken.lstbx_Gbr.Value = Cells(ActiveCell.Row, 3).Value
frmZoeken.txt_Zoekterm.SetFocus
End Sub
When opening the userform the following error message appears: Could not set the Value property. Invalid property value.
When I activate the row "On error resume next" the listbox is correctly filled. The error occurs with selecting the default value.
View 9 Replies
View Related
Mar 10, 2014
I can I select all non-blank cells (including constants and formulas) in a sheet? I am familiar with SpecialCells(xlCellTypeConstants) and SpecialCells.(xlCellTypeFormulas) but these seem to be mutually exclusive.
Can I do something like: NOT SpecialCells(xlCellTypeBlanks)?
View 2 Replies
View Related
Mar 17, 2014
I'm trying to select the next blank cell starting from a specific cell (N45) and then add a value from a textbox.
Ive got the following code but it isnt working.
Code:
ThisWorkbook.Sheets("RRHH").Range("N45").End(xlDown).Offset(1).Select
Selection.Value = NameTextBox.Value
View 8 Replies
View Related
Aug 2, 2006
I am trying to write a macro to paste some data from one worksheet into another. I need to paste the data into the next blank cell down from the existing data.
I have looked at this thread already
For w/o next error
but cant seem to get it to work.
This is the code I have got so far but it isnt working. any help would be much appreiciated.
'main part of code to copy data left out
Windows("performance tracker.xls").Activate
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=9
Set rngDestination = wsData.Range("A65536").End(xlUp).Offset(1, 0)
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = Format( Date, "dd-mmm")
Sheets(" Total").Select
ActiveWorkbook.Save
View 9 Replies
View Related
Apr 23, 2014
I am calculating multiple Correlations for long lists of data. I want the correlation based on the value in a given column. In the below example I would like a different correlation for each value in colA and the correlation will be between colC and colD. ColB lists the different people whose data are used for the correlations. In actuality there are 30 or more people for each test. In every case ColC is the same for a given value of ColB (although I doubt that matters). The number of people taking each test varies. I would like a correlation of ColC an ColD for each value of ColA.
Simplified version of my data
ColA ColB ColC ColD
Test1 Fred .75 1
Test1 Max .80 0
Test1 Sara .90 1
Test2 Fred .75 1
Test2 Max .80 1
Test2 Sara .90 0
....
Test100 Fred .75 1
Test100 Max .80 1
Test100 Sara .90 1
View 6 Replies
View Related
Feb 11, 2013
I need to query table contents where some general items are met, and item id's are not contained in an array.
Example:
Code:
SELECT LOOKUP.*, LOOKUP.Var FROM LOOKUP WHERE ((LOOKUP.Name)="test" Or (LOOKUP.Name)="test2" AND ((LOOKUP.Type)=3) AND ((LOOKUP.Var) ("array", "of", "items")))
I need to know how to add an array to a query since I can't hard-code the list each time, and the array can be a different size each time, so using array variables doesn't make sense here.
View 9 Replies
View Related
Jul 7, 2006
I am trying to assign an array element to select a specific cell then assign a value to it. Below is the general code that I am working with. Does anyone know why this is not working?....
View 8 Replies
View Related