I'm using Excel for holding lots of numerical data. Another software program pulls the information from Excel and is used to evaluate the data. The problem I am having is that some of the worksheets in my workbook have extra rows, and by "extra" I mean over 1,000 extra, blank, empty, rows. This means that in the other software program, it pulls over thousands of blank cells that have to be deleted every time it is opened.
View 6 Replies
I find (what look like) two extra toolbars have appeared at the top (below the standard and formatting toolbars) and to the left of my worksheet.
They have lines, dots and little boxes with an x or - in them, depending if you click on them or not. The +/- box function seems to be to make columns appear and disappear.
How did I turn this function on and more importantly, how do I turn it off?
I need to create extra rows of duplicate data. The number of duplicate rows depends on the number of semi-colons in the 15th column (column O).
Sheet 1 is the original working file. Sheet 2 is the result of how the executed macro for the first 2 lines of data. The first line in sheet 1 has 7 semi-colons, so there should be 7 rows of duplicate data created in sheet 2. The second line in sheet 1 has 6 semi-colons, so 6 duplicate lines, etc.
I have a sheet in an excel workbook which I export to a separate file and then save as a text document, I need to remove the tabs in this file, however the file (example attached) needs to be in a certain format to be imported into a piece of equipment which has a proprietary file format. Part of this format is the 2nd row and 5th row must remain present and empty.
[URL]
Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part
Code:
Sub CountRows()
Dim Rng As Range, CountTrue As Long
Set Rng = Sheets("Checklist").Range("Work")
CountTrue = Application.WorksheetFunction.CountIf(Rng, "True")
End Sub
coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.
As a simplified example:
Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!
So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.
I was thinking of using something like this:
Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Else
Rows("20:30").EntireRow.Hidden = False
End If
End If
I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.
I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.
This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.
I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.
I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies View RelatedHow do I populate a second worksheet with only the preselected highlighted rows of cells from another worksheet.
This way I will end up with only the selected data from the first worksheet in the second worksheet.
I would like to be able to select several non-sequential rows in a worksheet called "Data" (using a check box or just entering a value in Column A) and then be able to press a Command button to copy the selected rows to another worksheet called "Estimate" at the bottom of a table, and delete the designators in Column A (i.e. deletes the value, or unchecks the boxes) so I can repeat the process again if needed.
View 7 Replies View Relatedi've been scrolling through the Uerforms extra controls and just wondered if there is a comperhinsive list of them and what they actually do?
View 2 Replies View RelatedIn the below example each of the items listed in Row A have an extra space before the word, is there an easy way of taking that space out i've tried text to columns, paste special. Not sure what else can be done ...
View 9 Replies View RelatedHow do I trim an extra word. I downloaded a list with an address field and it has an extra city in the address. Ex. 908 Taylor St_Wake Forest_Wake Forest
How do I get rid of the duplicate state?
I have an excel spreadsheet with Extra columns that have no data in them. I want to delete this columns so that there is no horizontal scroll, but highlighting and right-clicking and choosing delete isn't working. Not sure why. How can I delete?
View 2 Replies View RelatedI have an excel sheet which contains the data in blue. In another sheet I have the same data but with an extra code (red). For my question I have put both in one sheet. My problem is that I need to combine the extra code info in the sheet with the blue data. Both the blue column and first red column are identical.
View 4 Replies View RelatedI finally got the perfect code that works for me, to list a folder with path name.
if I select any root like C:, D:, E:, an extra backslash is added. can this be fixed that, what ever folder or root drive I select only 1 backslash is added. here is the code
create a macro that will create csv files for each worksheet of a workbook. The Macro works great. But i do have a requirement where i do not need extra commas (,) in the CSV. When the CSV are created for each worksheet, it adds additional commas in the end and my shell script throws it away becuase of the additional commas.
I have attached 3 files -
csvtest.xls - this has the macro which creates individual csv files for EACH worksheet in the workbook........
I have values in cell like,
1,2,3 & 4
10,11
8,9,10,11,12
etc.
the formula to get min and max from this string
for the first value Min = 1 and max = 4,
for the 2nd value Min = 10, and max = 11
fo the 3rd one Min = 8 and max = 12
1) I work with dozen's of tabs within workbooks, adding rows (that are numbered) etc. When I'm done and want to save the file, I usually go through this routine to make sure I'm at the top of each page before closing the file:
Ctrl-Home, Ctrl-Page Up, Ctrl-Home, Ctrl-Page Up ...through all tabs.
2) I also have to "add" all the extra line numbers (by dragging the last number I had in "row A", and filling down). I need to add the newly added rows to the "print area". Any easy way to do that?
I am calling a procedure from a commandbar button using on action.
With oButton
.Caption = "&Progress Report"
.OnAction = 'this bit please
.BeginGroup = True
.FaceId = 576
End With
Private Sub ProcessingRequest(ByVal shtRequest As String)
End Sub
I've got the following code for the attached worksheet. My problem lies at the end of the code where I'm trying to determine the length of characters in column A. I get a zero value after the last row with an actual value.
aLen = Range("A1").End(xlDown).Row - 1
but that bypasses the problem, rather than fix it. By the way, if you run the code, make sure you only have the worksheet "1" in the workbook.
Sub Test()
Application. ScreenUpdating = False
Cells.EntireColumn.AutoFit
'Delete foot symbol from distances
Columns("E:E").Replace What:="'", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
'Sort & Copy Multipule pages
Cells.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("1").Copy After:=Sheets(1)
Sheets("1 (2)").Copy After:=Sheets(2)
Sheets("1 (3)").Copy After:=Sheets(3)....................
I've got this code that I use to open the active session of a program I use (EXTRA! 8.0). When I try to code it to open a specific new instance of the program, I get a run-time error 429. When I debug, it's stopping on the line where I get the new instance. I've searched on this issue, but I still don't understand what the problem is.
Sub log_in()
Dim Sys As Object
Dim Sess2 As Object
Dim lngOldSysTimeout As Long
Set Sys = CreateObject("EXTRA.System")
If Sys Is Nothing Then
MsgBox Prompt:="EXTRA! not properly installed on this PC." _
& " Terminating execution.", Buttons:=16, _
Title:="E!PC Not Properly Installed"
Exit Sub
End If
Set Sess2 = GetObject("C:Program FilesExtra!sessionsCCS2.edp")
If Sess2 Is Nothing Then
MsgBox Prompt:="DLC.edp not found. Terminating execution." _
, Buttons:=16, Title:="E!PC Not Properly Installed"
Exit Sub
End If
End Sub
i am writing Attachmate Extra Macro from excel to Attachmate. I have problem with that macro . where can i post to get answer. it is like vba code .
View 4 Replies View RelatedI have a workbook where I import data from another workbook. This might get done several times a month. As I go through the imported data I delete rows not needed which gets place into another worksheet. What I need is when I import data again is a macro that the worksheet where my already deleted rows are and compairs them to the new data imported and if the first 13 columns of data matches it delest them off the imported sheet.
I found some code which I altered at [url]
and posted the same question but haven't got any response as of yet.
So the code that i go works but only if the row matches in the exact order on both sheets so e.g. (if row 1 on sheet1 matches row 1 on sheet2) it works but if (row1 on sheet1 matches row 3 on sheet2) it does not.
Here's the code I have so far.
Public Sub delOLD()
Dim i As Long
Dim iLastRow As Long
Dim ws As Worksheet, ws1 As Worksheet
Set ws = Sheets("Data") ' Imported Data
Set ws1 = Sheets("Old Records") ' Deleted Data
'ws1.Visible = xlSheetVisible
the Ruler across the top that allows you to adjust the column widths (A B C D etc). Is it possible to insert another Ruler, further down the same page, to allow you to adjust the column width differently. if so how. On Excel 2007
View 2 Replies View RelatedI've created spreadsheet to look after scores & handicaps in a golf society. I'm trying to add extra spaces so that if new members join they can easily be added without me having to add all the extra rows,change formulas at the time.
However I'm getting stuck when trying to sort the table in 'Championship Leaderboard' sheet, basically I only want to sort actual playing members, not the ones marked 'Test'. I've only marked them as 'Test' whilst trying to do this, but they will be blank eventually.
Error values keep popping up, and as a result it won't sort. Trying to get rid of error values to equal 'zero' create problems elsewhere and I just seem to be going round in circles and getting nowhere.
Basically unless a new member is added i don't want anything to appear in the spreadsheet. But i don't want the formulas etc which are there in readiness to affect existing player entries and data
Is VBA the answer or can it be done with formulae etc,
I have some data in Cell A1 and B1, I want that data in combined in one cell but in diffetent lines.. For example if i have 123 in A1 and 456 in B1, I need it to show the result as below in C2
123
456
=IF(SUM(Q4)=0,"",MAX(0,G4-MAX(F4,Q4))/7) - to calculate number of days for year
=IF(SUM(TODAY()>G4),(G4-MAX(F4,Q4))/7,SUM(TODAY()-MAX(F4,Q4))/7) - to calculate number of days year to date
I am using this condition to calculate the length of a service which falls with in a specific review period, eg
Service starts 1 Feb 09 and finishes 30 April 09
The review period is 1 Mar 09 and finishes 31 May 09.
I would need to measure the number of days that fall within the review period, which would be 1 Mar 09 - 30 April 09
At the moment I am missing a condition which identifies whether to subtract from 'service end date' or 'review end date'.
using IF formula [ =IF(SUM(C3),LOOKUP(DATEDIF($C3,NOW(),"y"),(0,3,),(0,3,)),"")] holiday sheet after 5 years service employee get 3 days extra holiday trying to in E3 to add 3 days to holiday entitlement to take from 16 days to 19 days
View 2 Replies View RelatedMy production spreadsheet has thousands of unwanted columns and I am unsure as to how they appeared. I want to delete them. I have tried selecting all of them and deleting them, but nothing happens. I have tried go to > blanks, but it freezes because there are so many columns.
Also, I tried to copy paste to a new sheet, but the formulas get messed up.
For a mental picture, the spreadsheet is approx 225 rows by 25 columns.
