i want to do the same thing with the formula below, but for columns (i want to use the value in cell cw106 on sheet "POINTS" as the upper limit & cw107 as the lower limit.
Columns("aE:aF").EntireColumn.Hidden = True
i tired it like this and it wouldnt work
Columns(Sheets("POINTS").Range("cw104") & ":" & Sheets("POINTS").Range("cw105")).EntireColumn.Hidden
I have an issue with an export file which is produced in CSV format and needs to be aligned. I have attached a sample of what I refer to.
My Source excel file looks like:
ABC A-101 B - 202 DEF B-203 C - 100
The destination should align with the relevant column headers and place a blank if it doesn't match. Other have queried a similar thing, however my header and data and description is actually in the same cell with a dash separating them.
The output I want is:
A B C D ABC 101 202 DEF 203 100
There are multiple row of employees with varied column headings as its dependent on what system access the user has. The headings i.e. A,B,C for example is a fixed number of headers.
In my real data set the headers represents a 3 letter system prefix e.g. ZCR,ILP etc
I need to discern the first row where columns B or C or D or E become non-zero. This is because column A countains dates, and if I subtract one day from the date in the non-zero row I have the date of the analysis I'm examining.
The problem i have is that the code I've put together below gives me the date of the absolute last row in the spreadsheet, "31/12/2015 23:30:00" from row 70129 which isn't that useful. I'm expecting a date in August just gone which is on row 11618.
Code:
Sub test() Dim lngLastRowU1 As Long Dim lngLastRowU2 As Long Dim lngLastRowU3 As Long
I need to automate the attached as per the following (by not recording macro unfortunately).
Sheet one has a dump of data. First, I need to change the data format from yyyymmdd to dd/mm/yyyy. Then I need to filter for region EU and only copy the filtered data from columns A, C-D, F and the difference from columns I-J to Sheet 2. Then in Sheet 2, I need another column to calculate the difference per unit.
My excel file was imported from a 3rd party and has many blank rows across the entire sheet. I want to delete these rows if every cell across is blank. How is this accomplished?
I tried the formula "=isblank(C2:C18)" however I get a "False response on those rows that are completly blank.
i have data that will vary from item to item in the $ of rows associated and the # of columns to be dealt with.
item1 Line 1 Line 2 Here i want to have a formula put and copied across X columns item2 Line 1 Line 2 other data going across in the columns Line 3 Line 4 Here i want to have a formula put and copied across X columns item3 Line 1 item4 Line 1
get my meaning??
columns involved will be known before i get to this bit of code !! as an example lets say it's 8 columns
using the record macro i get this bit of code
Range("E14:M14").Select Selection.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)" This is fine but i don't know what row or the columns are before i run the macro so the Range ("e14:M14").select wont really work.
I've sent sometime going through MRexcell searching for tips but found nothing also googled for dynamically pasting across unknown columns
this is close to my idea in my mind conceptually Range("C2:C" & LastRow) but it doesn't make the columns dynamic eg column 8 being = H and then P = 8th column on and the last with data.
From the above thread I am using the following macro...
Sub MoveOver() Dim MoveRange As Range
Application.ScreenUpdating = False With Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(, 21)) .AutoFilter field:=1, Criteria1:="=C&P" On Error Resume Next Set MoveRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 .AutoFilter End With If Not MoveRange Is Nothing Then MoveRange.Insert Shift:=xlToRight End If Application.ScreenUpdating = True End Sub
I am getting an error 1004 message saying cells cannot be moved off of the spreadsheet. However, my last cell on the sheet is J28956.
Columns B through J contain data. Columns beyond J do not contain data.
Can someone give advice on getting the macro to work.
I know there have been many posts about multiple conditions in arrays, but I didn't find one that applied to my situation, so I'm hoping can help me out of jam. I'm using Excel 2000 on XP.
I have a database of sales information. Customer name is listed in column C, and columns F through Q contain the sales for the months of July - June (fiscal calendar year), with the headers for the months in row 4. There can be many rows of sales for the same customer, hence the need to sum them.
Example
C F G H CUSTOMER JULY AUGUST SEPTEMBER customer1 2 3 4 customer1 2 3 4 customer2 2 3 4 customer2 2 3 4
The problem is that this information will need to be updated every month to reflect sales year-to-date, meaning that the conditions for summing the data will change depending on how far into the year we are. So for instance, in August I will need to sum all the records for customer1 in the July column (column F) and the August column (column G), but next month it will have to sum the records for customer1 from July, August, and September (col H).
The result of this information would be displayed on a different worksheet. My thought was to assign a range of 12 cells (Sheet2!A1:A12) and to populate those cells with any of the months that need to be summed thus far. So for example, through September, A1 = "July", A2 = "August", and A3 = "September". Then the formula would use those values to evaulate the conditions. So in plain English the formula would:
sum the values in columns F : Q where the monthly header = the values in the range Sheet2!A1:A12 for all records where CUSTOMER = CustomerName
I have 40000 rows of data to check. I always filter Column E by Suppplier Name, like supplier Alpha or Bravo,Charlie,Donkey, or Elephant,France,Goal etc.
What I manually check is I filter the column E by Alpha, then I will get some 5000 rows of data of Alpha, I will look if all the rows in Column B contais "-3digit" numbers like -123,-548 etc if not I will assume its incorrect and copy the entire row and put it in next sheet.
If Column B is right that is, if it contains -3digit then I will again look in Column F ( Invoice Numbers) the invoice number should always be 5digt-4digit all numbers without space or any special charactors except Hypen(-). It may have 5digt-4digit-5digit-4digit etc, if any values doesnt match with the said criteria i will again assume it as incorrect and move to the next sheet.
Now once this is done i will filter againn Column E by "Bravo" all the conditions are same, that is if the supplier name is Alpha Bravo, charlie, Donkey, or Elephant then i will do all the checks mentioned above or else i will ignore all other supplier....
This code is pretty slow if most rows have to be hidden. I'm certain there is a faster way to do this but I'm not seeing it.
What am I doing with For each row from 12 to 236 I'm looking to see if there is data in any cell in columns A:F, H, & J. If not, I hide the row. I'm starting at row 236 and working up to 12 continuing to hide rows until I find data or I reach row 11. As soon as any row has data or row 11 is reached the the procedure ends leaving all remaining rows visible ...
Iam an accoutant and have been given a task we have a workbook we use monthly that has about 50 columns, we have to go in and hide all the columns we do not need to print so I am wanting to create one that will hide the neccessary columsn print, unhide the columns and then hide the neccessary ones and print and so and so on.
I tried to do this by recording a macro and it sent me to visual basic and now says ithas all of these errors and i dont know how to fix them i need to do this porbabaly for about 20 different hiding and printing combinations. I am going to attach the errors i am getting when i try to debug in visual basic this is just for the first combination so i guess i need to know how to do debug for the rest of the combiantions. I am also hiding the entire column i dont know if i should be or not. I am attaching the file that i am doing the hising in as well. So like for the first i need to only keep column a,b,c (always unhidden) and then g for this time, print, unhide, then again column a,b,c active, then column u print and then unhide and on and on.
I am attempting to creating a Macro in excel with an offset feature but I am having trouble getting it to work correctly. I will include what I have created thus far, but this is what I would like it to do. There are 2 columns, A and B which are header columns for rows 2 through 75. Columns C through G contain values which are referenced in formulas in the corresponding cells in columns H through the end of the sheet. Ideally after every day I would like to run the macro to hide the next 6 columns which are showing. So on day 1 I want to run the macro to hide columns H through M. On Day 2 I would like the macro to offset and hide the columns N through S and so on. Each day an additional 6 columns (which is the all the data for the previous day) need to be hidden so that the current day's data is showing and can be easily read alongside the headings which flow vertically down columns A and B. If you could please help me with the proper form of this macro I would greatly appreciate it. This is the current macro I have made, I would like to add an offset command in there but am unsure which line to tie it to:
Sub HidePreviousDay() ' ' HidePreviousDay Macro ' Hide the Previous 6 Columns (the entire last day) ' ' Keyboard Shortcut: Ctrl+Shift+H ' Columns("H:M").Select Range("H2").Activate Selection.EntireColumn.Hidden = True End Sub
I've recorded a macro in which I unhide certain columns, copy and paste some information then hide those columns again. The problem is that when the macro is finished, it incorrectly hides columns K to AN. I did not record that and it's not in the code so I'm lost as to why it's happening.
Why it's hiding everything from K to AN. I've tried recording the macro several times but it's just not working, no matter the order in which I hide columns when recording it.
I have an elaborate spreadsheet that I have made for my company. I will give a little background how I have it set up: E6 contains a drop down menu of all products that we sell, and line items are populated based on the selection here. In columns N and O, I have a "Business Partner Costs" table that calculates a specific discount % based on the business partner.
Some of our products are sold to BPs with no discount. For these specific products, I am trying to get the BP Costs table to disappear, or hide.
Basically, I want something like: "=if(or(e6=ae2,e6=ae7,e6=ae12,e6=ae13,etc),hide columns N&O,show columns N&O)" in macro format.
I have a spreadsheet set with the days in sach column (eg 29-Jan-09, 30-Jan-09, 31 Jan-09, 1 Feb-09 etc). What I would like to do is hide all columns prior to a date which is entered into a cell/macro text box.
I created a macro that analyzes some datasets and according to different parameters it puts an X at the side of every cell that goes out of parameters. These Xs are all in columns named the same way for it to be easier to find. What I want to do is to write some macro that will hide every row where the data did not go out of parameters to be able to easily see where it did. In the following example it would need to hide the second row since there are no Xs in that row. One of the problems I'm having is that the amount of columns and rows is variable.
Data Data Functional Upset Data Data Functional Upset
# # X # #
[Code] .......
Below is the piece of code that I created to try to do this (G has the value for the amount of rows). I think it is working but it is either taking too long since it has to sometimes go through over 20k lines as much times as it needs to to cover all the rows that contain Xs or its getting stuck for some reason.
Code: Worksheets(2).Range("I3:I" & G).EntireRow.Hidden = True K = 0 Do Until Worksheets(2).Range("I1").Offset(0, K) = "" If Worksheets(2).Range("I1").Offset(0, K) = "Functional Upset" Then
I'm trying to write macro for hiding columns based on a drop down answer. The drop down would be in cell B7 on Sheet 1. The colums affected would be in each worksheet in the workbook. The data validation would have a drop down selection choice of 1 through 16. The columns affected range would be H through W. For example, in the drop down if 1 was selected, only column H would show. If two, H and I would show...etc. This macro should affect all worksheets in the workbook EXCEPT sheet 1.
I am merging columns. Col B has all text in bold. Col C has all text in regular font. When I use ASAP to merge, the merged column comes out all bold. When I use a formula to merge the merged column comes out all regular. What I want is for the merged column to contain the bold text from Col B in bold, and the regular text from Col C in regular.
What I want is...
This is for service plan B: Take all data and transer to new server.
What I am getting is...
This is for service plan B:Take all data and transer to new server.
OR
This is for service plan B:Take all data and transer to new server.
I've created a spreadsheet that will be protect for use by others. I want to hide cells that are not used so it just leaves to print area in view and gives a simple and clear look.
I know how to hide rows, columns etc but there are always further rows and columns there. I'm sure this must be simple.
I have a VBA application that seems to work fine under vista but once I start working with it in Windows 7 I notice sometimes one of my userforms do not go away when asked to hide. The code is like follows:
I have two command buttons, and need one macro that would hide all blank rows between columns "E" and "O" and from row 9 to 42, and then another macro that would unhide all these rows again.
I have a spreadsheet with 5 worksheets going, I've been trying to find code that will hide and unhide certain tabs depending on the answer to C21 of Worksheet 1.
If C21 = 1 then show sheet 3 but not 4 or 5 If C21 = 2 then show sheet 4 but not 3 or 5 If C21 = 3 then show sheet 5 but not 3 or 4.
I am building a real estate model. What I am trying to do is the following:
If I enter the number of tenants in a cell (let's say the number of tenants is 6 and the cell I enter this number in is cell J11), I would like 6 of 15 rows to be shown below (rows 46-60). Therefore, the 9 unused rows (rows 52-60) would be hidden from sight. Furthermore, if I entered the number 3 for the tenant count, excel would hide 12 rows (rows 49-60). I am sure that this is a VBA code.
On the Form which I call my main menu, theres a help button, with this code
Private Sub cmdHelp_Click() MsgBox ("Help file not ready yet, good luck on your own, Have a Nice Day!") End Sub For some reason when this is click, and after the user clicks ok on the msgbox, the frmmain, which this command is on, minimizes itself
Seems like this should be pretty cut-n-dry to me...I thought I could "hide" an Autoshape (AutoShape 19) using some formula [I.E. =IF(B12="","",SUM(C37:C45)) ] but I cannot get it to work...
Basically I dont want to see the shape if there is no data D49.
