VBA - Selecting The Last Cell In A Column With Data
Oct 6, 2008
I am trying to write some VBA code to select a range in a column going down to the last item in the column.
So I want to write a macro that will copy cells B4 - B17 into cells A4 - A17 but am having trouble with the code that will select B4 - B17. When I use the usual code which is as follows:
ActiveSheet.Range("b4", ActiveSheet.Range("b1").End(xlDown)).Select
it selects down to A75, and I know why this is but not how to fix it.
This is raw data extracted from a third-party system. It has an option to export to Excel, but where it appears to have blank cells Excel actually believes there is data in there.
So is a way to identify what data Excel thinks is in there and perhaps use this to be able to select the range I want?
Different extractions will need a different range moved over - however it will always start at B4 and go down to B-whatever.
I 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 m trying to make a button to add values to another sheet in my xls. Ive done that... now i try to autofill the percentages from left and above one row....
I have a dropdown box to choose a category. When the category is chosen it displays the relevant course names which corresponds to that category (this could be 1 course up to 10 different courses). Along with that course name it gives the relevant course overview.
This works exactly how i want it to. (thanks to Zbor from a previous thread)
I now want to expand further and give the corresponding course objectives for each course that shows up in the results.
I have attached a workbook as its much easier to see the dropdown box working.
Sheet 2 is the working sheet and sheet 1 will contain all the source data. I have highlighted the columns affected in Blue Blue column on sheet 1 will have the course objectives on the same line as the course they belong to Blue column on sheet 2 is where they will be displayed when the correct category selection is made.
The course objectives are unique to the course title and course overview.
I putting together a spreadsheet that applies Payments (as they come in) against the oldest open invoice. As payments come in, old invoices are closed out. An aging is done and late fees are applied.
I have made a macro that inputs all of the information of the invoice in a list. However, when a payment comes in, I am trying to write a macro that:
1) in column G - Finds the first non-zero balance 2) Applies the payment amount to that open invoice. If there is additional funds left over after the satisfaction of that invoice, I would like the payment to be applied to the next open invoice until all the funds of the payment have been drained.
I am having so much trouble trying to even just locate the first nonzero balance and select it.
The match results are posted on another sheet and the winner allocated the letter W and the loser allocated L (in the sample workbook attached these are on sheet2 cells B1 and B2 with the winners name in A1 and the losers in A2, i.e. Bill and Susan) I have the code for this working properly.
What I need to do next is transfer the W and L onto the grid in sheet1 and insert the W in the cell that is the intersection between the row with the winners name and the column with the losers name. Then insert the L in the cell that is the intersection between the row with the losers name and column with the winners name. I hope that makes sense!
If it is simpler to insert the W and L into the grid directly from the winner and loser names in sheet2 col A rather than allocating the W and L in col B first, then that is great.
I am trying to write a macro, where I want to pick up alternative cells in each column from Sheet 1 and then Paste in Sheet 2. Each column starts with end of every year ( 31st Dec 1996 and so on. My data in sheet for each average column (for different years) is like this.
Average Closing Price 26.47
Stock Return -0.68
I want the value for stock return to be pick up different columns ( for each year) then paste in sheet 2.
I'm trying to make a spreadsheet that can be used to easily build a collective list of steps, for a user to read and follow line-by-line.
I want a source sheet of "steps" that I can change over time, and the resulting tabs that reference the source sheet get updated/populated automatically.
I've pieced together some VBA code from other sources, which kind of does what I want it to:
VB:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row > 1 And Target.Column = 1 Then Dim SourceSheet As Worksheet Set SourceSheet = ActiveWorkbook.Sheets("Steps") Dim TargetSheet As Worksheet Set TargetSheet = Target.Worksheet Dim c As Integer Dim Source As Range
[Code]...
But there are some problems:
1) The data auto-populates into a row. It would read better if each step was in the same column, meaning rows would need to be automatically added upon selecting something from the drop-down list. The number of rows that need to be added vary based on the number of steps in the source sheet, for the selection made from the drop-down list.
2) If you make a change to the source sheet, my goal is to have the other sheets referencing the source sheet's lists of steps update automatically, so you only need to update the steps in one spot and everything you've built from them gets updated instantly. Currently, you must select a different choice from the drop-down list, and then change it back, before it populates the "new" steps from the source sheet.
This is my first time using VBA.
What I have so far is attached: testAutoPopulate.xlsm
I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.
Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.
I have a workbook with many tabs and I am writing a macro that works on the active sheet but needs to pick up data from another tab, which is labelled on the active sheet.
As I want to write this macro only once but different tabs have different tabs they look to, VBA code that slects the sheet named in a fixed cell on the active sheet.
This is the code that currently looks up to the tab labelled 'Cork, FA'. However, if this tab name is stored in cell AA2, I would like the code to choose the tab based on the information in AA2?
is it possible to restrict a cell in such a way that it is impossible to type data into it, but rather select data from a drop down, through validation?
Amongst the several sheets contained in my workbook, there is one called 'Inspection Report'. Users fill in whatever data is required in the other sheets, and once they get to this one, they are supposed to enter a number from 1 to 3 into Cell X1 (which is currently selected) before they select anything else. Unfortunately, I am currently unable to stop them from doing what they should not be doing.
So, I would like to have a notification of some sort pop up into their face if they click or move the selection anywhere else while Cell X1 is still empty. Something like a validation would be nice.
In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.
I need to select the whole column ABOVE the active cell. Ctrl-Shift-UpArrow is no good because it stops at the first blank cell. And selecting the whole column is no good either because when I subsequently paste into the column, it pastes in all the empty cells of the column, meaning my worksheets expands from a few hundred rows to 1 million!
Shift-Home does what I want on rows. Is there an equivalent for columns?
I wrote a Macro that select info from Worbook "A" to Workbook "B". In that Macro tha columns B:B, E:E and F:F are selected.
The problem is that the layout is not always the same so the correct columns keep shifting positions. The only thing that never changes is the "Column Label" in the first Row.
So, no matter from which column I alway have to copy: "Order Date"; "Status" and "MetofPay".
Is there anyway to tell my macro to look for those columns instead of B:B, E:E and F:F?
augmenting current code to select the column after a column. Current code is:
Code: If Sheets(data_sheet1).Cells(2, iCol).Value = "Stations" Then TargetCol = 1 If Sheets(data_sheet1).Cells(2, iCol).Value = "Agency" Then TargetCol = 2 If Sheets(data_sheet1).Cells(2, iCol).Value = "Advertisers" Then TargetCol = 3 If Sheets(data_sheet1).Cells(2, iCol).Value = "Product Code" Then TargetCol = 4 The next line should find the column after "Product Code".
I have a table of numbers to which a new number is added every day. I want to reference the last value in a separate cell. For example, in cell A1 I want to insert a formula that will look at Column B and select the last value. The values in Column B wil be added to each day, ie a new row will be inserted.
I am creating a spreadsheet to manage Block ID conflicts when adding mods to a Minecraft Server. Example spreadsheet is attached.
Description: Column A contains a list of available numbers (e.g. 1-20 and 31-50). Column B contains a list of numbers a new mod tries to use up by default (i.e. 1-25). A number in Column B that is not in Column A must be changed to the nearest number that is in Column A. In this example, numbers 21-25 in B are not available in A and must be changed to 31-35, respectively. Column C should be the result of changing Column B (i.e. 1-20 and 31-35).
Issue: The main problem is that as a given cell in Column B changes, that number needs to become unavailable in Column A for the next change. For example, when 21 in B returns 31, 31 can no longer be used for the next number in B (22).
I have been able to use VLOOKUP or INDEX/MATCH functions to lookup the numbers to be changed.
I have a worksheet which already has some built in formulas which take the data in columns V and W, and use them to build other coding in column P. The trick is that the coding created in column P will be six times as long as the source data in columns V and W.
I.e., a single row containing "Sample1" and "Sample 2" in columns V and W respectively create the six following rows in column P:
I want to be able to select the accurate length of Column P, which should be 6x as long as columns V or W. Any dynamic way to do this? (Since the amount of data pasted into columns V and W will change each time I use this worksheet.) I know how to select set ranges, but not how to adjust them as multiples of the length of another column
I want to select a range of rows 11, 14 and 23 to 24 in column j, plus the 2 rows to the right of column J and run a sum formula on the range. My procedure is selecting rows 11/14/23/24 and doing the sum for only column J. How do I make it so it runs the sum formula on all 3 columns. My code is copied below. I am fairly new to VBA.
Code: With ActiveSheet.Range(Cells(27, j), Cells(27, j)).Select Set sumRng = Intersect(Range("15:21, 25:25"), Columns(j)).Offset(0, 2) ActiveCell.Value = WorksheetFunction.Sum(sumRng) End With
With wsSheet.Range("A:A") ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count) MyArray = wsSheet.Range("A:A") For i= 1 To .Rows.Count For j= 1 To .Columns.Count wsSheet.Cells(i, j+ .Offset(0, 3).Column) = MyArray(i, j) Next: Next
I want to select alla values in column A, but when I specify the range as "A:A" the code results in an infinite loop! How can I come around this?
I want to seach and select the newest date in a column. There are green, red and black fonts in the column. I only want to only seach the cells with black fonts.
I have a workbook that generates sheets for each year based on selected criteria. It starts at Column H and goes too AH and beyond. When my loop reaches Z it errors out. I think this is happening because the code is referencing the column as ASCII. Here is the
Sub Test() Dim d As Date Dim yrint, i, num_years, fields, field_start As Integer Dim yrstr, crit1, crit2, left_column_range_fixed, right_column_range_fixed, left_column_range_var, right_column_range_var, left_column_range, right_column_range, cost_column, cost_column_var, cost_column_fixed As String left_column_range_fixed = "H" right_column_range_fixed = ":AH" cost_column_fixed = "2" crit1 = "=x" crit2 = ">0" d = Date yrint = Sheets("Overall"). Range("H2") field_start = 9 'changed from 9....................
