Select Range Based On Last Row And Last Column Used
I have defined my last row and column by using:
lr = Sheets("week1").Cells(Rows.Count, "A").End(xlUp).Row
LC = Sheets("week1").Cells(1, Columns.Count).End(xlToLeft).Column
Now how do I select the range starting at A2 through the last used column ( LC ) through the last row ( LR )?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Highlight Row Range Based On Value In Another Column
I have been reading and searching on how to get around Excel's limit of 3(or 4) conditional formatting rules with VBA. I have a pretty good grasp of what the code is telling the system to do, however what I am not understanding is how can I have the Code check multiple sets of columns? ex Columns A,B and C Rows 1:100 need to be checked if they are between a particular range and clolums D,E, and F need to be checked against a completely different set of criteria. I tried cutting and pasting the Sub, changing the Range values and the conditions, but it always errors out.
View Replies!
View Related
Define Range Based On Named Row And Column
I would like to name a range based on a row number and a column number. I have found the row and column number using this code, but I can't seem to get the rest right. Sub LastRowCol() Dim intLastRow As Integer Dim intLastCol As Integer Dim ws As Worksheet For Each ws In Worksheets intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row intLastCol = ws.Cells.SpecialCells(xlCellTypeLastCell).Column
View Replies!
View Related
Cell/Range Names Based On Row & Column Intersection
i have a table in excel with 36 rows and 36 columns i.e. 36x36=1296 individual cells. Columns and rows have headers/labels i.e. 36 rows with row label headings and similarly 36 columns with column label headings. I would like to automatically create unique cell names for the 1296 cells based on where the columns and rows intersect using the row and column labels. The cell name format I would like to create is [ColumnLabel_RowLabel]. I've manually created the cell names in the attached file as an example of what i would am seeking (i've greyed the cells that I've done this for).
View Replies!
View Related
Select Row And Column
I want the entire row and column of the active cell to be selected - now only column "A:A" gets selected. Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.EntireRow.Select ActiveCell.EntireColumn.Select End Sub
View Replies!
View Related
Select Row Based On Cell Criteria
I am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View Replies!
View Related
Select Intersection Of Column And Row
First although it returns the correct column, it always returns Row 1. Beyond me! Secondly I could really use some help in a line or two of code to select the cell at the intersection of the column and row. Sub test() Dim name As String Dim number As String Dim col As Integer Dim row As Integer Dim RowNumber As Integer Dim ColNumber As Integer Dim rng As Range ActiveWorkbook.Sheets("Sheet1").Activate name = InputBox("type a name from Column A")...........
View Replies!
View Related
Select USED Range Minus 1 Row
I need to sort some data on a spreadsheet but I am uncertain on how to select the used range. I just want a piece of code that will select WHATEVER area of the sheet is populated with data. The sheet I will be doing the selecting on is titled "Master". To add to it I DO NOT want ROW 1 included in the selection.
View Replies!
View Related
Select Range With Row Variable
I am doing is setting a variable called eof to the number of rows (with text) and i just want to select columns A1 - G1 and the eof range! Dim eof As Integer With Sheets("Coverage Count (%)") eof = .Range("G65536").End(xlUp).Row 'crashes on the .select .Range("A1:G1" & eof).Select Selection.Sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With I've done this before and I could have sworn this worked...but i must be missing something.
View Replies!
View Related
Select The Last Non-null Cell In A Row Or Column
I am finding that I am using the 'go to' option frequently in my macros, but I feel very limited by the options that I have (I need to learn VB-I know) I would like primarily to discover how to select the last cell containing anything (not including blanks like the 'last cell' option in the edit>go to) within a selected row. Ideally, I would like a formula to fill downward a given amount of rows so as to prevent myself from having to fill them all the way down to the end of the spreadsheet (therebye making the file to large). Also, If anyone could guide me to a resource at which I can find similar commands.
View Replies!
View Related
Macro To Select Cell After Last Row In Column
the macro code to select the cell after the bottom of a column of data e.g. I have data in A1:A300 and I require the macro to automatically calculate the last row in the range (in this case A300) and then to select A301 (in this case) (I know I can record a macro but the number of rows can potentially differ every time I run the macro) N.B. Excel version 2003
View Replies!
View Related
UserForm To Select Row And Column From 2 Prompts
I have an appoinment calendar that is one month per worksheet - Column headings 09:00 - 20:00 - Row headings 'days and dates of the month' (split into two merged cells of two) ****************See attachment 'Calendar Format.jpg'**************** I have then created a UserForm which has two dropdown combo boxes ****************See attachment 'UserForm.jpg'**************** What I want to do is have a way of using the dropdown boxes to select a column and row reference to enter the other data into the four lines in the outlined box area (see Calendar Format.jpg) I want to be able to select a date - and to have only the dates from the sheet showing would be a bonus! - and have the script use that as its row reference, followed by selecting the time and having it add the column reference, and so selecting the correct cell to be able to then add all the other pieces of data. I would just set it up so that it all worked by selecting the first cell, then adding, but I'm worried about other users not doing that and so causing things to be misplaced or overwritten! I'm more than willing to change most things, but I do need to have those four pieces of info, and I only need to have a simple calendar. If anything is not clear, please ask and I'll try to clarify further! Forgot to add - In the pics I'm using 2007, but I want to be able to run this on 2000/2003 as that's what's in the offices and it's going to be shared on the server.
View Replies!
View Related
Using VBA To Select A Row And Colour Code It Based On Conditions
I've had a look through the forum and can't see an answer, so apologies if I'm duplicating something! I have a spreadsheet where column O has five conditions: 1 PO in 2 High Probability 3 Medium Probability 4 Low Probability Blank I need to colour code the whole row based on the value in the cells; if there were four conditions, I could do this with conditional formatting, but with five (with white as the fifth condition), I need to look at VBA. However, I'm having difficulty colouring the whole row; how do I do this? e.g. what I need to do is: If O2 = "1 PO in"; Select A2:T2; Colour green In addition, I need to ensure that if there is no data in column D, but column O is "1 PO in", it is highlighted in a separate colour. I have a macro for that, but don't know whether in needs to be put into the code before or after the macro needed above...
View Replies!
View Related
Programming A Macro To Select Row Ranges Based On A Formula
I'm building a macro thats copying 4 rows of data at a time from a spreadsheet (has 1500 rows of data total) and transposing it to a master spread sheet. There are blank rows in between each entry (the data source I'm using isn't clean), so how can I either have the macro jump to the first row it finds with a value, copy rows until it stops finding a value, paste+transpose into a new document and then repeat the process throughout the document. If thats too complicated, a macro that finds rows based on a formula would work as well. E.g. Start at row 5, copy+paste+transpose 5-8, start at row 11 (5+6), copy + paste+transpose 11-14, start at row 17 (x+6) ...etc... Here's the code I have developed thus far:
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
Select Column Based On ListBox Choice
If I've Listbox1 in "ABC.xls" which contain selected headers of workbook "XYZ.xls". How can I select entire columns in workbook"XYZ.xls" when press enter at Listbox ? Do I've to put the code here ? With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then ? ? ? End If Next i End With
View Replies!
View Related
Select Column And Delete Row Where Data Is Duplicate
I've got this macro which works well, but I have to edit it to change the column I want to check for dupes. I don't mind doing that, but now I have to share the macro with someone who is not comfortable changing the references in the code every time they run it. As it is it checks column G:G (7) for dupes and deletes the row. Good. I'd like for it to do the same thing, but for whatever column the active cell is in. I can use this: Col = ActiveCell.Column in place of the "7" in the With Range part but I don't know how to replace the "G1:G". Sub DeleteDuplicatesColumnG() With Application .ScreenUpdating = False .EnableEvents = False
View Replies!
View Related
Find Next Empty Row And Select Cell In Column A
how i can go about finding the next empty row (and select the first cell of that row (column A))? edit: It probably should be noted that there are cells in Columns A through P. There are rows where all and/or just one cell contains data per row. So i cannot use a " lookup" based on a single column.
View Replies!
View Related
How To Select Range And Clear Based On LR Variable
I want to select and clear a part of a sheet. here is what I have. Sub clear_data() sheets("sheet1").select lr = activesheet.usedrange.rows.count Here is where I get lost in translation (syntax). I want to select starting at Bcolumn through bycolumn but the row be set with the LR from above, since the rows always change. I could write B2:by2000, but i want to use the LR variable to define the number of rows i have.
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 Dynamic Range Based On Non-empty Cells
I require code to identify the last row in column 'A' that contains data, and then to select every row up to that one, and each column up to 'H'. My data begins on row 3, and the rows with data varies from row 7 through 120. The columns with data is constant so there is no need to test in that direction.
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
Vba Select Rows Based On Info In A Column, Then Paste Into New Workbook
I'm using excel 2003. I would like to select and copy rows of data within a worksheet of a workbook and then paste those rows into a newly created excel workbook. That is the easy part. The complication is that I would like the macro to select the rows based on a particular string value that will be impossible to specify in a fixed way (i.e., it will change from project to project). Let's say the variable that distinguishes the rows is location, so some are from Tokyo, some New York, and some from Paris. Using vba, how can one 'splice' the data into (in this case, three) seperate workbooks based on the string/values of another variable? I am familiar with auto-filter, but in this case, I really do need to create seperate workbooks, rather than apply calculations to a selection of the data.
View Replies!
View Related
Color Row Range Based On Cell On Same Row
I have an excel sheet in which i have a cell A11 with drop down list values=YES/NO. Now based on the value in this cell i want to fill color in the cells(B1 to B10) i.e for e.g. if i select YES in the cell A11 then the cells(B1 TO B10) should become green in color. Attached is an sample of what i want.
View Replies!
View Related
Range Object/property: Calculates 2nd Column Based On 3rd Column's Number
I'm trying to write a code that calculates 2nd column based on 3rd column's number or vice versa based on the condition set on the 1st column. Below, there are two procedures. "SimpleCalc" and "SimpleCalc2". I first wrote SimpleCalc2, but it isn't working, so I worked around the error by writing SimpleCalc, which selects a cell and moves around by offset. I personally find it hard to read and error prone as I develop more logic around it. I'm trying to develop more function based on this code, so I need to make it neat and flexible. what I'm doing wrong in SimpleCalc2? Or do you have any suggestion to improve the code "SimpleCalc"? I'm using Excel 2003. Sub SimpleCalc() Dim SimpleMethodRng, SimpleMethod As String, i As Integer ActiveWorkbook.Worksheets("Dashboard").Range("P5").Select SimpleMethodRng = ActiveWorkbook.Worksheets("Dashboard").Range("N5:P12") For i = 1 To 8 SimpleMethod = SimpleMethodRng(i, 1).........
View Replies!
View Related
Copy Column Range To Row Range
I have the following sheet1 (" Case Preview") column a column B column C ID project Custodian 1 ABC James Johns 2 DEF John Doe 3 GHI Laura DiNapoli 4 JKL Lydia Koernell I need to be able to display the custodians in column C on sheet1 ("Case Preview") in sheet2("Search Term Breakdwn") starting in B10,C10,D10,etc until all the names in column C are there. The tricky part is that column C gets populated each month with different custodians coming from different dbs, so I have to be able to know how many rows in column C are populated with info. I saw a few postings here regarding transpose and tried to used it, but to no avail. I am using the following Sub copyrows() Dim MySheet As Excel.Worksheet Dim MySheet2 As Excel.Worksheet Dim rcell As Range
View Replies!
View Related
Copy Range Column Based On Other Column
in column B of sheet1 i have every 3rd-4th row a name. i need to copy the name and the the data on the row below the name to sheet2, the list of names can be min 2 max 30. so sheet 1 has row 14- name in column B and C. in row 15 in columns d:z data for the name. i need to copy this to one row in sheet 2 copied for each name up to 30.
View Replies!
View Related
Select Entire Row & Column
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
View Replies!
View Related
Find Cell Based On Column And Row
I have a spreadsheet which has about 100 different SKUs in column A and the inventory for each SKU by period in columns B:Z So for example B would be P1W1 and C would be P2W2, etc what kind of lookup function would I use in VBA to find per say, the value of SKU #: "27017" in P1W2 ? Apart from pivot table.
View Replies!
View Related
Last Row And Last Column In Range
I have a macro to create a pivot table for a range of data. At the moment the input is defined as this: [code] ActiveWorkbook.PivotCaches.add(SourceType:=xlDatabase, SourceData:= _ "internal!R1C1:R" & lr & "C14").CreatePivotTable TableDestination:= _ "internal!R5C17", TableName:="PivotTable1", DefaultVersion:= _ xlPivotTableVersion10 [code/] so the range is from R1C1, to the last row(lr) and C14 What i want is for the range to be from R1C1, to the last row AND last column. I already have both the last row (lr) and last column (lc) stored so thats not a problem, the problem Im having is including lc in the range instead of C14 as everything I've tried flags up an error.
View Replies!
View Related
Delete Row Based On Range Selection
Here is my ThisWorkbook.sheets("sheet1").Range("M").Select For Each cell In Selection If cell.Value = ThisWorkbook.Sheets("Sheet2").Range("A").Value Then cell.EntireRow.Delete End If Next cell Range("a1").Select End Sub I want to remove all rows in sheet 1 that contain any value found in Sheet2 A I using XL 2003.
View Replies!
View Related
|
TRACKER
