Commenting Out Blocks Of Code
Apr 11, 2006a way to comment out a block of code just using a begin and end tag, as opposed to using an apostrophe (') for evey line?
View 3 Replies
ADVERTISEMENT
Add Names To Blocks
Oct 9, 2008In a worksheet I have (not equal) blocks of data. In column A there is the projectnumber, in columns B-K there are data. Projects are devided by an empty row. All rows in some block have in column A the same value (projectnumber).
What I need is the following: I would like to add names to that blocks. The name must be equal to the projectnumber.
Transposing In Blocks Of 10?
May 19, 2014I have a list of numbers in Col.A which can be variable in length.The objective is to copy these numbers into Columns with 3 col gaps in between.So the objective is that these numbers are listed horizontally (with 3 col.gaps separating each nr).To achieve this I have written the script below. However, as the orig.list of p/ns in Col.A can be variable, I would like to perform the transposing in blocks of 10 numbers.The transposed end product should be listed 10 numbers in each row (with 3 col gap between)rather than in one row. how to modify my vba script to do the above?
Code:
Sub TransposeSkippingColumns()
Dim c As Range
Dim i As Long
[Code]....
Numbering Blocks Of Data.
Nov 2, 2009I have 250000 lines of data and at the moment they are in seperate blocks of different sizes, and seperated by 5 blank lines.
For Example
112
1523
523
1523
*5 BLANK LINES*
12
23
*5 BLANK LINES*
344
4563
etc.
What I would like to do is give each block a number.
1 112
1 1523
1 523
1 1523
*5 BLANK LINES*
2 12
2 23
*5 BLANK LINES*
3 344
3 4563
The lines in between will come out eventually I just need them there as they are difineing the blocks of data.
Stacking Of Columns In Blocks
Apr 19, 2012I want to stack columns in blocks. Have not been able to work out a VBA code for the same. Let me explain the need:
- There are N columns of data, for e.g., Col. A, B, C and D has Data (total of 10 rows) ... 4X10 layout
- I want to stack columns in blocks as follows:
AB
AC
AD
... 2X30 layout
Increase Date By One In Blocks Of Four?
Jul 8, 2014I have a spreadsheet and I'm trying to add date to the left hand side. I want them to increase by one but there needs to be each date, in a column, 4 consecutive times.
Example
Jun 1 12p
Jun 1 4p
Jun 1 8p
Jun 1 12a
Jun 2 12p
Jun 2 4p
Jun 2 8p
Jun 2 12a
Jun 3 12p
Jun 3 4p
Jun 3 8p
Jun 3 12a
This is for a Dialysis flowsheet and I really need this to work.
Calculating Time Blocks
Apr 11, 2009I am trying to calculate the amount of time in blocks, using a pre-determined spread. For example:
AM block is anytime between 0600-1230
PM block is anytime between 1230-1830
Evening block is anytime between 1830-0000 and the Overnight block is anytime between 0000-0600
Sounds easy enough, yeah? Let me paint an example of how I want it to calculate though:
If an employee starts at 0600 and finishes at 1400, then the AM total is 6.5 and the PM total is 1.5.
If an employee starts at 1100 and finishes at 1900, then the AM total is 1.5, the PM is 6 and the Evening is 0.5.
If an employee starts at 2200 and finishes at 0800, then all of the 10 hours must report to the Overnight total, even though some of them fall within the Evening and AM blocks.
I just can't seem to get each block to show the accurate number of hours...and to bring in the rules that I require.
Remove Blocks Of Rows
Apr 27, 2006I have a worksheet that collects information from another using complex formulas.
In column A I have codes i.e. PRD001A, PRD001B etc. A block of codes starts at A and can go up to Z. i.e. PRD001 could start WITH PRD001A and finish with PRD001N then there is always a PRD001W and PRD001Z to finish with.
PRD002A would follow PRD001Z and might only have A, B, W & Z codes before PRD003A.
In column K is the sum of columns D:J.
What I want is a macro that will look at column A & K and if all OR some of the codes for say PRD001 are >0 don't delete any of the rows for that group of codes.
Then look at column A & K and if all of the codes for say PRD002 =0 delete all of the rows for that group of codes.
Linking Blocks Of Cells Across Sheets
May 1, 2014I have a master tab that has data grouped as follows
Column 1
title of a section (written once then left blank for the rest of a section)
Column 2
numerous descriptors (each in a new row)
Column 3
The response to the descriptors in column 2
I want to link entire sections from column 2 and 3 into different excel files so that if I make changes to column 2 or 3 in the master tab, the changes are made to all tabs. If there is a way, I'd like it if I add a row to the master, it is automatically added to the others.
Locking Specific Cell Blocks
May 7, 2014When cross linking cells from one file to another, I sometimes lose the source link to rearranging cells on one of the other spreadsheets. Is there a way I can "lock" the source cell so that it isn't lost as a reference?
View 1 Replies View RelatedCopying Blocks Of Rows Into New Worksheets
Jul 1, 2009What I'd like to do is to have the block of rows starting from "Agent:" and ending with "Total" copied into a separate worksheet and then have the worksheet named using the agent's name. I have highlighted the data in yellow in the attached reference file. I would like to have this done for all 58 agents. I could copy and paste manually but that wouldn't be the smartest way to go about this. I figure I probably need a looping structure for this but I am not yet proficient enough to figure this out quickly. I am supposed to have this done today.
Edit: The last part of this would be to have blocks of rows from the "sections" worksheet which use identical agent names also pasted below the data from the "questions" worksheet. I have attached the workbook I am working on now cutting and pasting manually.
Counting Blocks Of Highlighted Cells
Feb 18, 2010I have huge sheet with time periods across the top and accounts listed down. I have manually shaded cells red that represent "new" sales. I have used the custom function "colorfunction" to count these red shaded cells in the past. Now, I am trying to count blocks of cells. Each block is a different # of cells and represents sales $ over a different period of months. I want excel to count each block (start to end) ans add them up...
J F M Ap M....
acct 1
acct 2
acct 3
Sort Info In Column Into 50 Row Blocks
Jul 20, 2007I have a sheet with about 1000 rows of data filling 40 columns. the data is the same in column 1 for a random number of rows between 2 and 40. ie as here...
2
2
2
2
33
33
33
4
4
4
4
4
4
etc.
Is it possible with a macro to divide the sheet into blocks of 50 rows each with just the data before it changes to a different no.
so that it looks like this
2
2
2
2
46empty rows here.......................
Working With Time: Blocks Every Whole Hour
Aug 1, 2007I have a spreadsheet that users input the temperature each hour, for a 24 hour period. The time used is 24hr clock, as example, cell A1 will be 03:00, and cell B1 would be the forecasted temperature.
On another sheet, the user inputs the temperature for the specific time a airplane is going to take off, i:e: 07:32. Is there a way I can have the second sheet look back to the first sheet and grab the temperature for the correct time (using the hour block it falls into). The first sheet time blocks are every whole hour, i.e 08:00, but on the second sheet, the time could be any hour/minute, 07:32.
Counting Blocks Of Highlighted Cells
Feb 18, 2010I have huge sheet with time periods across the top and accounts listed down. I have manually shaded cells red that represent "new" sales. I have used the custom function "colorfunction" to count these red shaded cells in the past. Now, I am trying to count blocks of cells. Each block is a different # of cells and represents sales $ over a different period of months. I want excel to count each block (start to end) ans add them up... I can SEND THE SHEET IF YOU WISH....
J F M Ap M....
acct 1
acct 2
acct 3
Summing Uneven Blocks Of Rows
Apr 21, 2006I am trying to Sum "uneven blocks of rows" using the Sum Function.This works OK for the 1st.Block,but then the selection of cells moves down the same number of rows as the 1st Block.
I am attaching a sample file which best explains what I am trying to achieve.
Insert Rows Between Blocks Of Information
May 11, 2006I have used some code that Kris gave me a couple of weeks ago and changed it to insert a row between each block of information. Have I got it right?
Sub InsertRows()
Dim lRow As Long
Dim r As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
Application. ScreenUpdating = False
Columns("M").Insert
[m3] = "temp1"
Range("M4:M" & lRow) = "=LEFT(A4,5)"
For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(r, 13) <> Cells(r - 1, 13) Then Rows(r).Insert Shift:=xlDown
Next r
Columns("M:N").Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:= _
"H:HOME imcEXCELCOUPON_COUNT5_MAYREFORECAST.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
ActiveWindow.SelectedSheets.PrintOut copies:=1
End Sub
Compare Blocks Matrix Of Data
Aug 29, 2006I have the follwing data in A1:T17
3,4,7,9,10,11,21,32,33,35,37,41,47,57,60,64,69,72,74,75
4,6,9,10,12,13,15,21,22,31,35,47,49,52,56,63,64,72,74,75
4,6,9,10,15,21,31,33,34,41,42,45,46,47,57,60,68,72,74,78
3,4,6,10,11,13,18,21,30,32,33,35,46,53,60,67,69,74,77,78
6,9,13,16,21,22,31,46,48,49,52,61,63,64,69,70,71,75,78,79
3,4,7,10,14,17,18,21,28,31,33,36,37,43,47,57,65,69,75,80
4,7,13,15,17,25,29,32,37,42,45,47,50,57,60,64,68,71,72,74
3,7,10,11,16,18,28,34,35,43,47,51,52,55,56,57,60,64,71,72
8,9,10,12,16,21,22,28,38,47,49,51,52,53,54,55,64,66,71,72
4,5,6,9,12,15,19,20,30,34,35,38,45,47,54,56,63,65,72,78
5,6,9,12,15,21,26,31,32,43,44,47,64,66,67,68,69,74,75,80
..................
I want to compare the first set of 20 numbers (A1:T1) with the second set (A2:T2) and check how many numbers match. ** If the matched numbers
are >=10 ** then list them to the right of cell W1. In this example, 10 numbers matched are 4,9,10,21,35,47,64,72,74,75. List them in W1:AF1.
Now compare (A1:T1) with (A3:T3). In this example, 11 numbers matched 4,9,10,21,33,41,47,57,6,72,74 list them in W2:AG2.
Now compare (A1:T1) with (A4:T4). 10 numbers matched 3,4,10,11,21,32,33,35,60,69 list them in W3:AF3.
Compare (A1:T1) with (A5:T5). Here only 5 numbers matched 9,21,64,69,75 (Which is ** less than 10 ** - does not match the criteria) DO NOT LIST
this.
Compare (A1:T1) with (A6:T6). 11 numbers matched, 3,4,7,10,21,33,37,47,57,69,75 list them in cell W4:AG4.
.................
Sub Compare()
Dim r As Integer, rr As Integer, c As Integer, cc As Integer, opc As
Integer, opr As Long
opr = 1
For r = 1 To 16
For rr = r + 1 To 17
opc = 23
For c = 1 To 20
For cc = 1 To 20
If Cells(r, c) = Cells(rr, cc) Then
Cells(opr, opc) = Cells(r, c)
opc = opc + 1
End If
Next cc
...............................
Next r
End Sub
The above code runs fine but If I increase my rows from 17 to 1200 it takes 3 1/2 hours to complete. Can it be reduced?
Copy Data Range Blocks
Jan 8, 2008I would like to copy specific cells (all in one block) to another worksheet, then do a bunch of other things with the data (these steps work already), then go back to the original worksheet and copy the next block of data, repeat about 50 times, then stop at the end of the list.
I can't attach a file as I can't get it under the file size limit but the rows to move (just to any worksheet in the workbook, from there I can manipulate and move as required) each time are row 1 plus all those pertaining to each subject group. So for group 1 it would be row 1 plus rows 6 to 28 - for group 2 row 1 plus rows 29 to 51 - group 3 row 1 plus 52 to 74 etc. If I can just get looping through chunks of data and moving them around under my belt
Sort Range Data Blocks
May 9, 2008I have a list of data (as shown below under Input), which I would like to sort them according to their block number (as shown below under Output). The hurdle is the sorting should be able to cater for missing fields. Also, note that the sector can be recurring, that is "P,P,Q,Q", and then follow by "P,P,Q,Q" again.
INPUT
Block No.SectorData
11P19
22P
33P39
41P18
52P21
63P38
75P22
85P24..............................
Picking Blocks Of Data To Copy From One Sheet To Another
Jul 22, 2014I have a series of spreadsheets that represent the rosters of teams in a league. At the end of the league season, some teams qualify to advance to a final event, and some do not. I start with a file of all the teams for that level of competition. This list comes from a program that creates it's output in an excel '97-2003 format (and I can't change the way the data comes out of this program). I modify the data a bit by removing data from merged cells and rename that worksheet a 'Raw Data'. In the past I have manually copied and pasted the data for the teams that advance to a sheet called 'Roster Data'. I am hoping there is a way to automate this part of the chore.
There must be 6 blank lines between the team names on the 'Roster Data' tab, but some teams have more members than others, so they are not spaced the same on the 'raw data' tab. Is there a way to select teams from the 'Raw Data' tab and have only the selected teams show on the 'Roster data' tab, AND have the spacing become uniform?
Dynamically Adding Row Blocks Based On Cell Value
Apr 2, 2014I've managed to get coding that allows me to insert single rows dynamically based on a cell value. Example is if I enter a value in cell C3 the coding inserts the same number of rows below.
I've also created a simple macro that inserts a multiple row block under the active cell, complete with formatting, text and some merged cells. Example is I run the macro and it will insert a block of 3 rows, complete with formatting, borders, text and merged cells.
My challenge is to bring these two elements together. So enter a number in a single cell which will then insert the same number of row blocks, complete with formatting etc. I've added a file to sample what it is I'm trying to achieve. I can add the coding I already have as well.
Dynamic list sample.xlsx
Split Address Blocks Across Multiple Cells
Feb 20, 2006I have cells of block addresses that are delimited (tab I believe - there's a
small square that appears).
I need to split the addresses out into separate columns for each line of address. I tried using the Text to Columns wizard as suggested by Excel Help, but ended up with only the first line of address being posted in the cell to the right (the rest of the address was nowhere to be seen).
I'm sure I've done this before but can't for the life of me remember how I did it.
Large Dataset - COUNTIFS On Blocks Of Text
Nov 3, 2011I have a large dataset. under the heading Theme there are blocks of text, ie not single words. i have created a simplified version below
Sample table:
StateThemeVictoriatradeNew South Walestrade, ownershipNew South WalestradeVictoriaownership,
test textVictoriatest text, tradeVictoriatrade, ownershipVictoriaownership, trade
I want to count, for example the number of times the words 'ownership' OR 'trade' appear for Victoria.
The formula i am using is doubling up on the last 2 entries.
This is what I have:
=SUM(COUNTIFS(A2:A8,{"Victoria"},B2:B8, {"*ownership*";"*trade*"}))
How To Copy Blocks Of Formulas Or Conditional Formatting
Sep 11, 2007Lets say I have a block of cells (lets say A1:E5) that all reference the value in the upper left hand cell (A1). Could be a formula by itself or one embedded within a conditional format. I reproduced the formula and formats in the A1:E5 block by using the absolute cell reference $A$1.
I now want to reproduce that entire block (including the referenced cell A1) multiple times on the sheet, but the catch is that each new block must reference the cell in IT'S OWN upper left hand corner. (e.g. a block located at F10:J15 must use as it's reference F10, not A1).
How can I quickly reproduce those blocks? I have done a search and replace to eliminate the $ symbols within the formulas to make them relative, but that doesn't seem to work within conditional formats.
The issue right now is I am trying to apply conditional formats to a sheet I already constructed. I don't want to overight the formulas in the UL corner, so I am trying to copy and paste the conditional formatting only. Can't seem to figure out how.
Move Blocks Of Data In 1 Column To Rows
Nov 15, 2006Moving Data And Deleting Blank Rows. I am a complete newbie to excel VBA and require assistance (if at all possible). I have data in the following format in an excel sheet (I have a lot more rows but give an example for two rows)... in 1 column but I wish to separate it (i.e. move data along onto the first row in other columns) and delete blank rows.
For Example:
Col1
AAAA
BBBB
CCCC
DDDD
EEEE
AAAA
BBBB
CCCC
DDDD...............................
Rearrange, Move Blocks Of Cells To New Columns
Apr 19, 2008I saw a post that needed a solution for moving blocks of cells in order to get fewer rows but more columns. The post is gone but I'd like to think my work wasn't in vain as the algorithm was more difficult than I thought. The problem was wanting make a 5000 X 4 grid into a 1700 X 12 grid, 55 rows and 4 columns at a time. This is sometimes done for visiblity or printing purposes to get more data going across instead of down. An example would be wanting to have more data accross on a print page and the page holds 55 lines of data.
For example, A56:D110 would be moved to E1:H55, A111:D165 to I1:L55, A166:D220 to A56:D110 etc. The code is somewhat generic so different blocks of rows and columns can be specified. To see this code work, enter the number 1 in columns A-D, then fill series -> step by one for a thousand or so rows. Then run the code.
Sub FewerRowsMoreColumns()
'Rearranges blocks of cells so the end result is more columns and fewer rows
Dim RowStop As Long
Dim ColStop As Integer
Dim RowStep As Long
Dim ColStep As Integer
Dim RowOffset As Long
Dim rw As Long
Dim col As Integer
ColStop = 12 'Enter the last column number you want the cells moved to
RowStep = 55 'Enter the number of rows you want to move at one time
ColStep = 4 'Enter the number of data columns you are starting with.....................
Sort Records Within Data/Range Blocks
Apr 19, 2008I have around 50,000 records in a data file within which groups of records are contained in blocks delineated by date/time. e.g.;
A B C D E
10/03/2008 13:15 a 100.0
10/03/2008 13:15 b 2.8
10/03/2008 13:15 c 50.5
10/03/2008 13:15 d 8.6
10/03/2008 13:15 e 32.5
10/03/2008 13:25 75.2 <-----Time change
10/03/2008 13:25 5.5
10/03/2008 13:25 16.5
10/03/2008 13:25 3.5
etc
As you can see, while the file is ordered by date/time in Column A, the values in column E are randomly ordered.
My objective is to maintain date time order but sort the values in Column E into an ascending order within each block. So, for example, using the details above I would want it to look like;
A B C D E
10/03/2008 13:15 b 2.8
10/03/2008 13:15 d 8.6
10/03/2008 13:15 e 32.5
10/03/2008 13:15 c 50.5
10/03/2008 13:15 a 100.0
10/03/2008 13:25 3.5 <-----Time change
10/03/2008 13:25 5.5
10/03/2008 13:25 16.5
10/03/2008 13:25 75.2
Note that there is data in columns B to D which would need to be sorted in line with Column E. So, all records in the block, cols A to E are sorted to give the above order for each time block.
My problem is that I'm not sure whether a basic sort can do the job or whether a macro is needed. I'm using Excel 2003.
How To Create Expandable Cells For Large Blocks Of Text
Apr 25, 2014create cells which could be expanded/shrunk, to either show the first sentence or so of the essay, or the full essay. I'd need to be able to expand/collapse each cell individually, not by row or column as the tutorials I've found have shown. I'm pretty sure I've seen this done with a little +/- sign in the corner of the cell but I can't find the option. It would be a bit like on a website that have show more, show less buttons.
View 2 Replies View RelatedClubbing Blocks Of Data For Two Entities In A Column Chart
Mar 11, 2009Clubbing blocks of Data for two entities in a Column Chart
I need to show graphically the comparison between two LOAN amounts for a Housing LOAn..
I am almost done with the MATH part part of it, however need to compare the results Graphically...
I have two Blocks of DATA primarily: ...