Autofilter Block Of Data From Active Cells Offset

Jul 16, 2012

Thats the best I could describe that What I face today is the means to run a VBA Autofilter from an Activecell. The Activecell is a search result. I have a table that spans from A1 to E5000. The Activecell will always be in Column A. What I need to happen in when the Activecell is found an Autofilter is placed in the block of data from Column B to E. The rows will change after every search hence my dilemma. Each block of data has its own header as well

So if my Activecell is A2 then I need B1:E4 Autofiltered then Column E sorted Smallest value to Largest. The Activecell needs to remain static. The size of the blocks of data are exactly the same.

Here is a photo example of the desired result. Untitled.jpg

There are hundreds of blocks of data like this.

View 7 Replies


Offset With Autofilter

Aug 4, 2007

I have a spreadsheet with a "freeze pane" 5 rows down from the top. My data is entered from row 6 down. In row 3, I have an offset function that offsets 0 columns and 3 rows, which returns the value of the first data entered (on row 6). On row 5 I have an autofilter but when I use it, the "offset" function still shows the data in row 6, rather than the data that is now on the top because of the autofilter. Does anyone have any ideas how to solution this? Maybe a diferent function? The "Offset" function always needs to show the uppermost value in the data range, that is from row 6 down, whether the filter is applied or not.

View 5 Replies View Related

Autofilter With Offset Criteria

Dec 4, 2011

This is the following code i have a problem with:

ActiveSheet.Range("$B:$J").AutoFilter Field:=1, Criteria1:=Sheets("Report").Cell(0, -2).Value

I am trying to filter items on a sheet called "all the answers" and i want to look up a value on the sheet called "Report" to filter.

The cell i want to filter is two cells to the left of the active cell on that sheet.

View 5 Replies View Related

Active Cell And Offset

Nov 26, 2008

ThisWorkbook.Sheets(2).Cells(nrow + 1 + j, 2 + high_count).Formula = "=VAR(" & ActiveCell.Offset(0, n_high_count -1) & "," & ActiveCell.Offset(0, -1) & ")"
*n_high_count is the negative of high_count

I want "ThisWorkbook.Sheets(2).Cells(nrow + 1 + j, 2 + high_count)" to have the formula "=VAR(XX:XX)" where the range is the current row second column to the current row current column -1.

View 9 Replies View Related

AutoFilter Non Active Worksheet

Oct 2, 2007

I have a chart that gets updated to show the desired reporting period by clicking a button to run some code which applies filtering to the source worksheet using a value selected from a drop down validation list on the chart worksheet. When the code runs, the source worksheet displays temporarily. How do I prevent this switching back and forth between the data and chart worksheets during code execution? Undoubtedly, there is a better way to code this.

Sub Chart_FilterPPM()
Application.DisplayAlerts = False
wk = Worksheets("Charts"). Range("D63")
Worksheets("Leak Data").Activate
With Worksheets("Leak Data")
.AutoFilterMode = False
End With
With Range("Headings_LeakData").AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=wk
Selection.AutoFilter Field:=11, Criteria1:=">5000", Operator:=xlAnd
End With
Application.DisplayAlerts = False
End Sub

View 3 Replies View Related

Offset Active Cell To Beginning Of Row

Jul 28, 2006

I need to make the active cell go to the beginning of the row (Column A). I tried setting up a variable to be the row.value, but that didn't work (the way I wrote it, which doesn't surprise me).

View 6 Replies View Related

Autofilter Active Column Non Blanks?

Mar 25, 2014

I need a VBA code to do the following:

- The active cell i.e. the selected cell can be anywhere on row 11 (this is a merged cell with row 12 but I can unmerge if causes problems)

- The filter needs to be applied to the rows below the active cell (at most this will be about 10,000 rows below)

- I need to filter for non-blanks only

- Above row 11 there is one blank row and a number of filled rows; these need to be ignored i.e. excluded from the filter

- In this case I will need the filter to work on the cell with 'France Trade Product' in it

- The filter would need to hide rows below this cell except for row 16 where a non blank is found


View 1 Replies View Related

AutoFilter Using Active Cell As Criteria

Apr 29, 2008

My excel file has a column for customer name (there are more columns btw). I have the filter feature on. The customer list is in column F.

I recorded a macro by copying a customer (from the customer column, say from cell F99). Then I click on the filter arrow and select the 'custom' option and in the dialog box that comes up, I paste the copied customer and try to find other records which have the same customer name.

This works great but when I run the macro for some other customer, say from cell F200, the pasting part, pastes the customer that I had selected when recording the macro.

Is there a solution to make this generic so that the macro will work in a manner that I simply highlight the cell of the desired customer and then then run the macro which will give me the result.

View 7 Replies View Related

Offset With Automatically Find Active Cell With VLookup

Jul 30, 2012

I have 2 columns of data

column A = weeks (A2:A50) i.e. P1W1, P1W2, P1W3....P12W4 etc
column B = headcount (B2:B50) i.e. 5, 7, 5...10 etc

Essentially my criteria is "looking for last week and give me headcount" i.e. my lookup criteria is P7W5 ....however if P7W5 has no data, i want the lookup to go up or offset to the row above (it may be one to 4 rows above)....

View 3 Replies View Related

Change Fill Color. Offset From Active Cell

Dec 8, 2006

I am aware of the ColorIndex, but more specifically I need to change the fill color of a cell that is 9 columns to the right of the active cell. I can't find out how to make this work. to clarify:

ActiveCell + 9 columns.Interior.ColorIndex = Yellow

There has got to be an easy way to do this.

View 3 Replies View Related

Store Active Autofilter Criteria In Array Or Range

Nov 18, 2008

I want to perform some operations (basically a secondary filter) based on the values which are currently filtered within a single filter column.

are great, but what if there are more than 2???

i.e. I have a column containing values L01 to L20.

My column is filtered on L05, L06 and L07 (or some other combination).

I want to extract the values and L05, L06 and L07 and do what I will with them.

View 9 Replies View Related

Macro To Go Down Active Cells With Data?

Oct 18, 2012

i have the following macro that copies data from sheet2, sheet 3, sheet 4 and consolidates it into "master" sheet.


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim ofs As Long
If Sh.Name = Sheet2.Name And Sh.Name = Sheet3.Name And Sh.Name = Sheet4.Name Then
ofs = CLng(Mid(Sh.Name, 2)) - 1
Sheets("Master").Range("C9:BB400").Offset(ofs * 500).Value = Sh.Range("C9:BB500").Value
End If
End Sub

i am not well versed with macros

1) the macro is not going to sheet2, sheet 3, sheet 4
2) how can i make my range dynamic. for example, my range is C9:BB400.
-can i make it so the range is C9:BBxxx (where xx is the last active data cell in the range?)

it will prob take a pro a few seconds.

View 3 Replies View Related

Blanking Cells Within Block Of Cells If Date Parameter Has Not Been Met?

May 30, 2014

Sample Chart.xlsx

I need to enter data every month for the following month (i.e. month end close, for previous month). For this example, let's say I just entered (in May) data for April. Current year is 2014. The attached above is what I have currently, and what I want to achieve. All of the cells have formulas, but I basically don't want data and variances shown for time periods not yet uploaded, so in my example, I should only see filled-in cells for January-April, and not May-December (keep in mind that 2013 data should remain, as these are time periods in the past). I've thought about many scenarios including: modifying the formula but not changing what it is currently doing (essentially gathering and adding data from a different worksheet), conditional formatting to "white out" all cells for months where 2014 data is not available using NOW or similar function. how to do it most efficiently without disrupting what's currently in place. T

View 14 Replies View Related

Pasting Cells Using Offset Command Based On Variable Cell Data?

Mar 3, 2014

I am trying to paste data into cells that are offset from specific data in my excel sheet.

The code I've got so far is:

[Code] ....

What I am aiming for is to search my Columns G and H that contain the values 1 & 1. Then from the cells that contain those variables, move 2 row up and 7 columns to the left and then paste on that active cell.

I am completely new to VBA so im sure there plenty wrong with this VBA script but so far ive managed to paste the data but it just pastes across the whole row instead of just the cells ive copied.. now i just get errors on the script.

View 5 Replies View Related

Freezing A Block Of Cells

Jun 16, 2009

I need is for a block of cells (A1:I4) to be visible at all times. I've frozen the sheet so that the top four rows are always visible, but unfortunately the sheet is quite large, horizontally-speaking, so that when users scroll horizontally, important stuff scrolls off the screen.

View 9 Replies View Related

Inserting A Block Of Cells Using An If Statement

Jun 26, 2008

task of inserting a block of cells into a worksheet.

I have saved 4 different blocks of cells and depending on an option chosen from a drop down menu i want to select one of more of these sets of data and paste them into a worksheet.

View 10 Replies View Related

How To Freeze Specific Block Of Cells

Sep 18, 2013

I want to freeze rows 1-18 and columns A-P. But when I select the the cells and choose freeze panes but what it freezes is Rows 1-13 and columns A-H.

View 1 Replies View Related

Sum / IF Function Resulting To Calculation Of Block Of Cells

Jun 3, 2014

I have quarters of the year. I'm wanting to find a function wherein when I punch in for example year 2012, the cell would give me the sum of the 4Qs. If if change it to 2013, then it would give me the sum of 4Qs for 2013.

See attached sheet : sum from blocks of cells.xlsx

View 3 Replies View Related

Format Multiple Cells Into Address Block?

May 3, 2013

how I can get this:

John Smith

1 Aisleby Avenue




i.e. each line in it's own cell
to look like this:
John Smith
1 Aisleby Avenue

In other words, an address block in a single cell. Simple merging only saves the first cell's contents.

I need to do this for 500 addresses

View 2 Replies View Related

How To Count And Report Instances Of Text In Block Of Cells

Feb 17, 2014

I have a workbook consisting of two sheets: a Monthly Class Schedule (Divided into five one-week blocks. Each block's vertical is Mon to Fri, the horizontal is 9 class slots. There are 45 class slots pw). Each class has one student and the cells are filled 'Student Number, First Initial, Surname' eg, '666 J Smith'. Students may be scheduled for several class slots per week and some class slots have no students scheduled. When the student attends a 'P'is added at the beginning of the cell eg,'P 666 J Smith'.

The Student Attendance Report has the students listed vertically on the left, arranged by Student Number, and then a column for each week.

At the moment I have to manually count the classes each student has attended for each week and enter the attendance figures in the appropriate week column. It's a PitA so I want to automate the process by using the S#s in the Attendance Sheet to search the week blocks in the Schedule Sheet for 'P S#' and auto-fill the week columns in the Attendance Sheet.

Sched and Attend February 2014.xls

View 1 Replies View Related

Excel Block Hidden Cells When Paste In Another Workbook?

Feb 21, 2014

How can I block the hidden cells, so that when I want to copy/paste into another workbook, pasted data has to be all, except hidden&locked cell?

View 4 Replies View Related

Sum Data Block Between Two Dates

Apr 11, 2012

I have a database with dates in coloum F, coloums G to Q is where different product tonnages are entered. Cell Report C2 is the start date and cell C3 is the end date. I can calculate the total tonnage of a product (coloum J) bu using the formula

= Sum(Data!J:J)-Sumif(Data!F:F,""&Report!C3,Data!J:J)

This works but now if I want to total from coloum G through to coloum Q between the dates I cannot get it to work.

View 2 Replies View Related

Conditional Formatting Formula Used To Turn A Block Of Cells Green

Jan 27, 2010

I have attached a small file with a very simple conditional formatting formula used to turn a block of cells green.

However, the formula does not seem to work properly as it only turns half of the cells green. Columns B1 to B18 are also supposed to turn green, but instead remain clear.

View 11 Replies View Related

Formula With Offset From Active Cell And Sum Until Blank Cell

Dec 24, 2013

I have a formula in cel B1 : =SUM(A2:A100) / A1

I would like to use this formula many times in the sheet, so I would need a reference to the cell the formula is in, and have the SUM range until the next empty cell one column to the left.

So I would need something like (literally):

=SUM(Offset activecell (1,-1) : Offset activecell (1, (look for next empty cell -1)) / Offset activecell (0,-1)

View 3 Replies View Related

Copying Block Of Data To Another Sheet?

Feb 12, 2014

I am looking out for a code where I can copy a block of data to the second sheet and adding the new block of data below the previous one.

The block of data ranges from E6 to K15. On a click of the command button the data gets copied to sheet2 starting from A2. It will be pasted in sheet2 from A2 to G11. When I click the command button with the new data it will be added from A12 to G21 after that A22 to G31 and so on It will keep on adding the block.

View 4 Replies View Related

Put Data In Column B As Common Row For Each Block

Dec 30, 2008

I have a matrix with info of persons of different cities in consecutive rows, where the
city data is in column B. I would like to eliminate columnb B and divide all persons from
any particular city with 2 new rows, their city name and below a field for every column data.

*The number of employees for city would be variable.

Original data (The columns used go from A to E)

1 City_1 Employee_1 Address_Employee_1 Other data_Employee_1
2 City_1 Employee_2 Address_Employee_2 Other data_Employee_2
3 City_1 Employee_3 Address_Employee_3 Other data_Employee_3
4 City_1 Employee_4 Address_Employee_4 Other data_Employee_4
5 City_2 Employee_1 Address_Employee_1 Other data_Employee_1
6 City_2 Employee_2 Address_Employee_2 Other data_Employee_2 ...

View 9 Replies View Related

Automatically Create Set Of Charts From Block Of Data

Jul 12, 2012

I have a quite big database as a result of a study in Excel 2010. There is a special block of data for every participant in the study, including 16 data series in 16 lines. I have to make a unique chart for every line.

One block is bulid up in a way, that every row is the input for a chart, with the exception of one "spacer" row, which is empty.

1 2 3 4 ...


For example in the sample above, there should be 6 line-charts (A, B, C, D, E, F) next to the block.

Can a macro be written, so I can automatically create the chart-set by selecting the block's upper left cell and running the macro? Or is there any easier method?

View 5 Replies View Related

Taking Multiple Data Block To Another Sheet

Mar 3, 2007

I need to use Excel 2007 in a stock sheet situation.
I have attached a picture of what the sheet will look like.
What I need is a formula that will take a value in column c and then add the value in colum a behind that. Preferably with a X or - between them ie.

If i took the first row and applied the formula it should kick out 5 x Jam
The main thing is it must output the answer to another file/page so that the main page stays the same. And if there is no value in Colum c it must ignore it.

View 9 Replies View Related

Error Code Selecting Block Of Data From Worksheet?

May 3, 2014

I am trying to select a block of data from a remote worksheet but I keep on getting the following error

Run time error '1004':

Application-defined or object-defined error.

I have attached the file and I am working on worksheet 21 "PT Monthly Report 1"

View 7 Replies View Related

Get Value From Another Cell Using Offset And Active Cell

Nov 11, 2009

Get value from another cell using Offset and active cell. I can do this in VBA but I cant seem to do it on a worksheet.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved