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?
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 RelatedI've been working on a macro which delivers a daily report. the report is made up of 4 sections (received orders, scheduled orders, pending orders and unusual orders) each section is topped by a title which is a merge of all columns (A through I) the problem I am having is that the first column displays case numbers (to be displayed in text or number formats) and the 7th and 8th columns are dates When I try to have the VBA select the first column to format as a number, it will select all columns due to the merged cells is there a way to format only non-merged cells of a columns
This is the section I currently use for formatting but it doesn't actually work, (everything ends up in a date format)
[Code] .......
i want information on my "Intra-op tab" to automatically populate on my "Tissue" tab. In Column "A" of my intra-op tab i enter case id's, 1 per row, and i would enter a date received under column "D". On my "Tissue" tab, i also enter those case id's under column "A" but i would have multiple rows of the same number. I want that whenever i type a specific case id on my "Tissue" tab, it will automatically fill in the date in column "T", the date that coincides with the case id in column "D" of my "Intra-op Kit" tab.
View 1 Replies View RelatedIs 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.
As the title sugests I would like to Select a cell and a macro will highlight the row and column
Sub Select_Entire_Row()
Dim RowNo As Integer
Dim ColNo As Integer
RowNo = Selection.Row
ColNo = Selection.Column
If RowNo.Value >= 1 Then
Cells(RowNo, ColNo).EntireRow.Select ' I want it to do both not one then the other
Cells(RowNo, ColNo).EntireColumn.Select
End If
End Sub
How to make the below selected range, which represents column Q, also include column A?
Range(ActiveCell, ActiveCell.End(xlDown)).Select
I have data that i import on a daily basis, the data can range from a couple of rows to thousands,
What I need to do using VBA is select the active cells, where cells in row ''G'' is blank then delete the entire row where that cell is.
I have a spreadsheet with multiple columns. I need to save values from each specific column, but they are not non-contiguous, to a .txt or .csv file. In other words, I may need B:10, B15, B12, B25, etc. saved as one string in a text file. I have been working on this for about 15 hours and can't seem to get it to work.
View 9 Replies View Relatedi 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
I want to be able to copy cell D4, I4, G10, G12 etc from one spreadsheet and paste into another spreadsheet in the same locations as the source.
Is there a macro that will do this?
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 8 Replies View RelatedThe 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
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 RelatedI have a column that has mostly empty cells. I want to select only the ones that have data so I can perform an operation on all of them. Empty cells must be excluded from selection. I do not know where the bottom of the data is.
The solution needs to be in VBA where it is part of a larger macro affecting the user's worksheet.
I am trying to select a range based on two variables which store the column numbers. what I have is:
View 4 Replies View RelatedI am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.
I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.
If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.
I am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
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,
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.
I have this code:
Modules --> Module1:
Code:
Function Find(strSearch As String) As Range
Dim aCell As Range
Set aCell = ActiveSheet.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
[Code]....
I've walked this through the debugger. The Find function is finding the "Applicable" column fine (column 2). But how do I convert the aCell object to a range so that Intersect will be true, and will uppercase the cell value that was updated? If I were hardcoding this, I would return Range("B:B").
I need automatically deleting of whole row based on cell value, if value of cell which is in h:mm format, is less than or equal to 2:00 (h:mm)
So far I got.
Code:
Sub FillDownFormula()
Code:
Range("L2").Formula = "=RC[-3]-RC[-2]"
Range("L3").Select
Columns("l:l").Select
Selection.NumberFormat = "h:mm"
Dim rng As Range
[Code] .........
how do I apply a formula I entered into one cell to all the cells in that column, without going through the tedious process of typing the formula in again in each cell ? I've done it before but don't remember how.
View 2 Replies View RelatedI'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:
View 2 Replies View RelatedI have tried several methods to delete the entire row if the cell in column 'A' is blank ...
View 9 Replies View RelatedI am trying to write a macro to do the following:
Search column "I" for blank cells. If one is found then all adjacent cells to the left in that row are shifted to the right by one. (in other words for that row cells A-H are shifted right to become cells B-I).
Search column "I" for blank cells. If one is found then all adjacent cells to the left in that row are shifted to the right by one. (in other words for that row cells A-H are shifted right to become cells B-I).
View 2 Replies View RelatedI'm trying to make the below two column selections into a selected range to clear out formulas/values in the range.
Current code segments:
ActiveCell.EntireColumn.Select
Columns(ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column).EntireColumn.Select
Is there a way to combine the two code segments into one selected range?
I have a sheet that begining in A3 and going down need to look for the First Instance of the text String "Loan Documents" and down to the last instance and select the corresponding range in column B.
Example: if the Text String appears in Range(A14:A32) I need the Range(B14:B32) to be my selected range.
I seem to be going round and round in circles with this, but I'm sure it should be easy.
I'm just trying to select a range of cells in Sheet2 of my workbook.
I've tried many different bits of code, including:
Dim namesTotal As Integer
namesTotal = 2500
Sheets("Sheet2").Activate
ActiveSheet.Range(Cells(1, 1), Cells(namesTotal, 8)).Select
(According to the Microsoft website, this is supposed to be the way to do it?)