Looking through the Excel articles, I was inspired by the article by Akuini Excel VBA: easy way to paste to visible cells. My code below takes into account hidden rows and columns in both source and destination. It also ensures that overlapping cells of source and destination are not copied; it...
Sub Manpower()
Dim lastRow As Long
Dim Wrkb1 As Workbook
Dim Wrkb2 As Workbook
lastRow = Worksheets("Manpower Details").Range("C" & Rows.Count).End(xlUp).Row
Set Wrkb1 = Workbooks.Open("D:\DU\MIS.xlsm")
Sheets("Manpower").Select
If ActiveSheet.AutoFilterMode Then...
Hi, I would like help with a macro to select visible cells in specific columns of a table.
My aim is to manually filter my table, then run this macro which will select data from only the visible cells in three columns. Each column data will be copied to another sheet.
I already know how to...
Hi,
I have coded for a solver problem in macros. But the problem is the range in B column is auto filtered and the other rows are hidden. So as per inputs, only the rows in B column that satisfy the input are shown. It can start at any row. How do I include the code to use only visible cells in...
Hi MrExcel, I need your help.
I have an autofilter applied to a range resulting in non-continuous set of rows visible (see snapshot).
Assume I want to access the number of the first visible non-header row (which is number 7 in this case). When I run
Dim rng As Range
Set rng =...
Hello,
I have a file that contains multiple groupings of merged cells, as well as many cells are not merged.
I need to be able to filter a column and have all of the visible rows auto-fit the height so I can view all the text within (some will be merged and others will not be).
I have found...
Wondering if anyone can help. I need help to create a macro for putting an "x" value into the visible cells i've filtered on a column.
Since the column may not be the same on each sheet I'd like a pop up message to ask Yes or No to ask if the correct cell is selected at the top of the column...
Hi, I need some help optimizing (or completely replacing) some code. I have a dataset across 12 columns and ~3k rows which have a combination of vlookup and sum formulas. After the vlookup is finished evaluating (5-10 seconds) I would like to replace the formulas for only these cells to be the...
How could i use something like ActiveCell.Offset(1, 0).Value = "" to check if the next visible cell (not next cell down) while using autofilter is blank?
ActiveSheet.Range("$K$2:$DF$199").AutoFilter Field:=61, Criteria1:="<>"
If ActiveCell.Offset(1, 0).Value = "" Then
Else...
Hi,
I have a Table of information in cells F2:P1259; there is data in columns F-L and values regarding the data in columns M-P. I need a formula that chooses the visible cells in column M when I filter in columns F-L.
I have a percentage number in cell L1261 that I need to multiply with...
Hello All - first time using VBA in a while.
I have some pretty basic code I need to tweak to select the visible cells in a range. If it's not asking for too much, I'd also like to make this range dynamic (the number of rows is the only thing that varies, number of columns will be constant)...
Hi
I have a data set which is filtered. I have supplied the original data set, the cells in formula format, and the subsequent filtered data.
I calculate a formula in column F from data in each row. I then calculate a running balance in column G, which simply adds the formula in column F to...
Hi,
I'm trying to analyze data from a survey where we assign a point value to each response and then average the scores for each demographic. I'm running into a problem when I start to filter the data based on location/role of the user. I'm using a countif formula to count the number of...
Hello Guys!
I am working on a dashboard with a pivot table and I am trying to make it as automatic as possible.
To do so, I would like to be able to activate different pivot fields (data, which corresponds to a specific week).
The weeks' data are put into different columns.
e.g. If I want to...
Hi there,
I'm looking for a method to display whichever option is chosen from a drop-down menu to appear in a separate cell.
I have a range of data which fall into particular categories, but the list is in no particular order. For example, in column H, I have days of the week...
MONDAY...
I have a series of swipe card "beeps" in excel 2010 for the month of april. They are sorted by date per each employee. There are multiple "beeps" per day since the employees go from the production floor to the cafeteria, home, or to the restroom
I need to device a macro where I can calculate...
Hi
I have a list of names, and I am using a formula to calculate the total of individual names. Extract of spreadsheet is below (sorry, I'm not sure how to format it better in this post)
ColumnA ColumnB
Name1 1
Name2 1
Name2 0
Name2 0
Name3 1
Name3 0
Name4 1
The formula used in Cell B2 is...
I would like to be able to find the largest visible area of continuous rows in a filtered table. I know one possible way would be to loop through visible cells using the "xlCellTypeVisible" property and count cells in each visible area. However, the data is consisted of tens and sometimes...
Hi,
I am writing a VBA code, where I need to copy the visible cell before AF53 from multiple files and paste them in another file. For that I need to figure out how to select the particular cell as it may be in different columns in one or the other file
Thanks in advance
Folks:
How do I "ActiveCell.End(xlUp).Select" without the "End" or ActiveCell.Offset(-1, 0).Select to the next visible cell?
I know "ActiveCell.End(xlUp).Select" gets me to the next populated cell or end of contiguous data and
ActiveCell.Offset(-1, 0).Select gets me to the cell right above my...
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.