Free Microsoft Excel 2013 Quick Reference

Adodb recordset object, columns and rows transposed

Hi All,

I'm using an ADODB connectino from excel 2007 vba to an oracle database to query data from teh database and return it to my workbook.

Everything is working excep the final step in which I take the rows from the adodb recordset object and put them into my workbook.

in this code is the adodb recordset

  Dim resultset As Variant
    resultset = sqlresults.GetRows
    
    .Range("A2").Resize(UBound(resultset, 1) - LBound(resultset, 1) + 1, UBound(resultset, 2) - LBound(resultset, 2) + 1) = resultset
It works, but it is transposing the columns and rows. So if I were looking at my query results in my oracle sql editor I'd be seeing rows but the same information is put into columns in the excel workbook by the vba.

Is it possible to get it to flip things back around without writing some kind of double nested loop thing to transpose the array?

Thanks,

Dave


Hello OzGrid. I am looking for some assistance with transposing columns and rows in an Excel worksheet. This data is a table of part numbers requested vs. received over several different days. Currently, the data is sorted by date across the columns, and each date is split into two columns for requested and received. The rows are labeled with the individual part numbers.

I would like to format the data so that the part numbers appear as the column label, and each part number label is split into two columns for requested and received. Also, the date would then signify the date of each value in the table. I have attempted to use the transpose command, but this only gets me half-way there - still struggling with the splitting of the columns. I can drag a simple "=CELL" command, but the existing data is too cumbersome to build this. I thought a macro might be more efficient.

The file attached is a snap-shot of the data, but will display the formatting challenge I have. The worksheet "Given Format" is the current, while the "Desired Format" is the desired.

Thanks in advance to all the helpful folks in OzGrid.

I'm using Excel 2003 and I have a question about combining columns and rows
into a row. The data is from a vendor program and it tracks employee
activities. The data export from the vendor that puts the data into 4
columns, with a minimum of 3 rows. The columns a CustomerID, Name,
RecordType, and Record. It is the RecordType column that is repeated at
least 3 times. Below is an example:

CustID Name RecordType Record
1 Doe, John Activity Delivery
1 Doe, John Frequency 3 days/week
1 Doe, John Current Yes
1 Doe, John Activity Stock
1 Doe, John Frequency 3 days/week
1 Doe, John Current Yes
2 Smith, Jane Activity Front Desk
2 Smith, Jane Frequency Daily
2 Smith, Jane Current Yes
2 Smith, Jane Activity Loading Dock
2 Smith, Jane Frequency Weekends
2 Smith, Jane Current Yes
2 Smith, Jane Activity Call Center
2 Smith, Jane Frequency Holidays
2 Smith, Jane Current No

What I'm looking for is this:

CustID Name Activity Record Current
1 Doe, John Delivery 3 days/week Yes
1 Doe, John Stock 3 days/week Yes
2 Smith, Jane Front Desk Daily Yes
2 Smith, Jane Loading Dock Weekends Yes
2 Smith, Jane Call Center Holidays No

Until we get the vendor to fix their export function, how can I achieve the
above without using the transpose function? The export has over 25,000 rows.
Any help would be greatly appreciated. Thanks!

Hi,
I have been strugling with this one, I learned of some scripts here at this forums on how to launch outlook and copy columns and rows to it.
However, is it possible for the Macro to only copy the rows only to the extent where there is data?
Below is an example, it copies from B6 till V500. At times I only have data until V200 or maybe less. Is there a workaround for this? I mean copy only to outlook those columns and rows with populated data.
Please advise?

Thank you.

Sub Email_Create()

    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    
     
    Set rng = Nothing
    On Error Resume Next
    
    Set rng = Nothing
    On Error Resume Next
    Set rng = Selection.SpecialCells(xlCellTypeVisible)
    Set rng = Sheets("Sheet1").Range("B6:V500").SpecialCells(xlCellTypeVisible)


    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.createitem(0)

    On Error Resume Next
    With OutMail
        .SentOnBehalfOfName = " "
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Test Email"
        .HTMLBody = RangetoHTML(rng)
        .Display
            End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True

    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
 
End Sub
 
Function RangetoHTML(rng As Range)

    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"
 
    
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
 
    
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
 
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
 
    
    TempWB.Close savechanges:=False
 
    
    Kill TempFile
 
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    


End Function


I am trying to write a macro to, among other things, hide multiple columns and selected rows. Everytime i record the macro and play it back it hides not just the columns and rows i selected to hide but everything. then it prints and unhides everything. Any and all help or ideas are much appreciated.

P>S If anyone would like to see the actual workbook message me and i will email it to you as it is to big to attach to this post.

Code Below


	VB:
	
 IRDPAYSLIP() 
     '
     ' IRDPAYSLIP Macro
     '
     '
    Rows("29:65").Select 
    Selection.EntireRow.Hidden = True 
    ActiveWindow.SmallScroll Down:=-30 
    ActiveWindow.ScrollColumn = 2 
    ActiveWindow.ScrollColumn = 3 
    ActiveWindow.ScrollColumn = 4 
    ActiveWindow.ScrollColumn = 5 
    ActiveWindow.ScrollColumn = 6 
    ActiveWindow.ScrollColumn = 7 
    ActiveWindow.ScrollColumn = 8 
    Range("A:A,B:B,F:F,H:H,I:I,J:J,K:K,L:L,M:M,N:N,Q:Q,U:U,W:W").Select 
    Selection.EntireColumn.Hidden = True 
    ActiveWindow.SelectedSheets.PRINTPREVIEW 
    ActiveWindow.ScrollColumn = 7 
    ActiveWindow.ScrollColumn = 5 
    ActiveWindow.ScrollColumn = 4 
    ActiveWindow.ScrollColumn = 3 
    Rows("5:5").Select 
    Range("C5").Activate 
    Selection.EntireRow.Hidden = False 
    Rows("4:4").Select 
    Selection.EntireRow.Hidden = True 
    Range("A1:W28").Select 
    Range("C1").Activate 
    ActiveSheet.PageSetup.PrintArea = "$A$1:$W$28" 
    With ActiveSheet.PageSetup 
        .LeftHeader = "" 
        .CenterHeader = "&""-,Bold""Hamburgers R US" 
        .RightHeader = "&""-,Bold""Sam Richardson" 
        .LeftFooter = _ 
        "&""-,Bold""H:Computers2011Unit 2785 Hamburgers 'R' Us" & Chr(10) & "Month Of July" 
        .CenterFooter = "" 
        .RightFooter = "" 
        .LeftMargin = Application.InchesToPoints(0.708661417322835) 
        .RightMargin = Application.InchesToPoints(0.708661417322835) 
        .TopMargin = Application.InchesToPoints(0.748031496062992) 
        .BottomMargin = Application.InchesToPoints(0.748031496062992) 
        .HeaderMargin = Application.InchesToPoints(0.31496062992126) 
        .FooterMargin = Application.InchesToPoints(0.31496062992126) 
        .PrintHeadings = False 
        .PrintGridlines = False 
        .PrintComments = xlPrintNoComments 
        .PrintQuality = 600 
        .CenterHorizontally = False 
        .CenterVertically = False 
        .Orientation = xlLandscape 
        .Draft = False 
        .PaperSize = xlPaperA4 
        .FirstPageNumber = xlAutomatic 
        .Order = xlDownThenOver 
        .BlackAndWhite = False 
        .Zoom = False 
        .FitToPagesWide = 1 
        .FitToPagesTall = 1 
        .PrintErrors = xlPrintErrorsDisplayed 
        .OddAndEvenPagesHeaderFooter = False 
        .DifferentFirstPageHeaderFooter = False 
        .ScaleWithDocHeaderFooter = True 
        .AlignMarginsHeaderFooter = True 
        .EvenPage.LeftHeader.Text = "" 
        .EvenPage.CenterHeader.Text = "" 
        .EvenPage.RightHeader.Text = "" 
        .EvenPage.LeftFooter.Text = "" 
        .EvenPage.CenterFooter.Text = "" 
        .EvenPage.RightFooter.Text = "" 
        .FirstPage.LeftHeader.Text = "" 
        .FirstPage.CenterHeader.Text = "" 
        .FirstPage.RightHeader.Text = "" 
        .FirstPage.LeftFooter.Text = "" 
        .FirstPage.CenterFooter.Text = "" 
        .FirstPage.RightFooter.Text = "" 
    End With 
    With ActiveSheet.PageSetup 
        .LeftHeader = "" 
        .CenterHeader = "&""-,Bold""Hamburgers R US" 
        .RightHeader = "&""-,Bold""Sam Richardson" 
        .LeftFooter = _ 
        "&""-,Bold""H:Computers2011Unit 2785 Hamburgers 'R' Us" & Chr(10) & "Month Of July" 
        .CenterFooter = "" 
        .RightFooter = "" 
        .LeftMargin = Application.InchesToPoints(0.708661417322835) 
        .RightMargin = Application.InchesToPoints(0.708661417322835) 
        .TopMargin = Application.InchesToPoints(0.748031496062992) 
        .BottomMargin = Application.InchesToPoints(0.748031496062992) 
        .HeaderMargin = Application.InchesToPoints(0.31496062992126) 
        .FooterMargin = Application.InchesToPoints(0.31496062992126) 
        .PrintHeadings = False 
        .PrintGridlines = False 
        .PrintComments = xlPrintNoComments 
        .PrintQuality = 600 
        .CenterHorizontally = False 
        .CenterVertically = False 
        .Orientation = xlLandscape 
        .Draft = False 
        .PaperSize = xlPaperA4 
        .FirstPageNumber = xlAutomatic 
        .Order = xlDownThenOver 
        .BlackAndWhite = False 
        .Zoom = False 
        .FitToPagesWide = 1 
        .FitToPagesTall = 1 
        .PrintErrors = xlPrintErrorsDisplayed 
        .OddAndEvenPagesHeaderFooter = False 
        .DifferentFirstPageHeaderFooter = False 
        .ScaleWithDocHeaderFooter = True 
        .AlignMarginsHeaderFooter = True 
        .EvenPage.LeftHeader.Text = "" 
        .EvenPage.CenterHeader.Text = "" 
        .EvenPage.RightHeader.Text = "" 
        .EvenPage.LeftFooter.Text = "" 
        .EvenPage.CenterFooter.Text = "" 
        .EvenPage.RightFooter.Text = "" 
        .FirstPage.LeftHeader.Text = "" 
        .FirstPage.CenterHeader.Text = "" 
        .FirstPage.RightHeader.Text = "" 
        .FirstPage.LeftFooter.Text = "" 
        .FirstPage.CenterFooter.Text = "" 
        .FirstPage.RightFooter.Text = "" 
    End With 
    ActiveWindow.SelectedSheets.PRINTPREVIEW 
    ActiveWindow.SmallScroll Down:=-12 
    Rows("23:25").Select 
    Range("C23").Activate 
    Selection.EntireRow.Hidden = False 
    Range("C67").Select 
    ActiveWindow.SmallScroll Down:=3 
    Rows("28:66").Select 
    Range("C28").Activate 
    Selection.EntireRow.Hidden = False 
    ActiveWindow.SmallScroll Down:=-9 
    Columns("C:AC").Select 
    Selection.EntireColumn.Hidden = False 
    Columns("B:B").Select 
    Selection.EntireColumn.Hidden = False 
    Columns("A:A").Select 
    Selection.EntireColumn.Hidden = False 
    ActiveWindow.ScrollColumn = 2 
    ActiveWindow.ScrollColumn = 3 
    ActiveWindow.ScrollColumn = 4 
    ActiveWindow.ScrollColumn = 5 
    ActiveWindow.ScrollColumn = 6 
    ActiveWindow.ScrollColumn = 7 
    ActiveWindow.ScrollColumn = 8 
    ActiveWindow.ScrollColumn = 7 
    ActiveWindow.ScrollColumn = 6 
    ActiveWindow.ScrollColumn = 5 
    ActiveWindow.ScrollColumn = 4 
    ActiveWindow.ScrollColumn = 3 
    ActiveWindow.ScrollColumn = 2 
    ActiveWindow.ScrollColumn = 1 
    ActiveWindow.SmallScroll Down:=-3 
    Rows("5:5").Select 
    Selection.EntireRow.Hidden = False 
    Selection.EntireRow.Hidden = True 
    Selection.EntireRow.Hidden = False 
    Rows("4:4").Select 
    Selection.EntireRow.Hidden = False 
    Rows("5:5").Select 
    Selection.EntireRow.Hidden = True 
    Application.ScreenUpdating = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


i recently downloaded an example spreadsheet from this site "DisplayWaitMessage". It had a nifty little set up where alot of the columns and rows are deactivated. how do you do that?

(its probably some siimple thing that i will really feel stupid not knowing)

I need some help on this one. I want to create a large spreadsheet in which I can reference the intersecting cell of a column and row based upon criteria entered in other cells. A simple example: I want to enter a "model number"(columns) and multiple "option numbers"(rows) and obtain the value of the cell(s) where the two points intersect. Your help is appreciated. see the attached sample for clarity.

Mike

Hi Everyone,

Need some serious help. I am making a sheet that needs to have columns and rows a certain size for one portion of it, but different sizes below. But all on one sheet.

For example:

cellA4 needs to be 48" wide but cell A10 needs to be only 25" wide.

How do i go about doing this??

Thank you.

Hi,
I am new to this forum. I use Excel but just for basic spreadsheets. I am now the testing coordinator and I have a lot of data I have to analyze. How do I take certain columns and rows and have it go to a new spreadsheet. Let me explain.

I only want to extract the 10th and 11th graders that have taken the Science test. The columns that I need are titled as such:

Science Raw Score (AI) being the column it is in.
Science Scale Score (AJ)
Science item correct response (AK)
Science Item Student Response (AL)

The student's information is:
Grade Level (B)
Last Name (E)
First Name (F)
Student ID (H)

There is much more information on this workbook that I do not need. I only need the science results from the 10th and 11th grade. Not the reading, social studies so forth. So as you can tell this worksheet has many columns. My final report I just want to be able to look at each individual student in 10th and 11th grade that took the Science test and their scores and answers. Hope this makes since. Thank you.

Hello..

I am new at vba and I would like some help on this problem that I am having. I want to write to the cell where the column and row meets base on column and rows names.

Let say I want to write to the cell where the column name = "Col3" and row name = "Row3". In the picture below D4 is where the column name = "Col3" and row name = "Row3" meets. How do I write "This is where they meet" in D4??

I guess it requires 2 loops that loops through the columns and rows??

Please help.....

Please look the picture attachement.

In Sheet1 there are n number of rows and columns with data. I would like to fill the series in Sheet2 based on the number of columns and rows in Sheet1.

For example, in the attached spreadsheet, I have data (shaded yellow) in 16 rows (A2 - A17) and 12 columns (B - N). In this case, I would like to fill the series 1 to 16, 12 times in Sheet2 starting from cell A1. Could someone help me with a VBA code to achieve this? Thanks.

Hi Guys,

Need your help in resolving the same. I need to freeze the column and row height and width for a specific area in spreadsheet, so that it shouldnt be altered in future from any one using this sheet. Is it possible ?? pls let me know. Thanks.

Thanks in Advance

Regards-
Guru

I have data in columns A through to J (i.e. 10 columns) and rows 1 to 400. So there is a huge block of data. I need to purely move the data in columns B to J (in sequence) so they sit under just one column A.

In other words I will have a long column A comprising of the same data but all in one column, which will end up being 4000 rows long.

I wondered if there was a quick way to do this? I have no experience of scripts or anything like that so would appreciate a dummies approach to this.

Thanks

I can't seem to change the font and font size of the column and row labels in Excel 2007.

The problem is the data in my spreadsheet (cell data) is font size 8 and the column/row headers are about 12 - that is throwing off the look of the spreadsheet.

Any suggestions? Thanks,
Jim

Two issues with this code. (Probably more to be discovered!) First although it returns the correct column, it always returns Row 1. Beyond me! Secondly I could really use some help in a line or two of code to select the cell at the intersection of the column and row.

Thanks in advance.

winger
Code:
Sub test()

Dim name As String
Dim number As String
Dim col As Integer
Dim row As Integer
Dim RowNumber As Integer
Dim ColNumber As Integer
Dim rng As Range

ActiveWorkbook.Sheets("Sheet1").Activate

name = InputBox("type a name from Column A")
number = InputBox("type a number from Row 1")

Cells.Find(name).Activate
col = ActiveCell.Column
Cells.Find(number).Activate
row = ActiveCell.row
RowNumber = ActiveCell.row
ColNumber = ActiveCell.Column
  
Set rng = Intersect(Rows("RowNumber"), (Columns("ColNumber")
rng.Select


MsgBox "found and the row number is " & RowNumber & " and the column number is " & ColNumber

End Sub
Sheet1

*ABCDEFG1*OneTwoThreeFourFive*2Alpha******3Bravo******4Charlie******5Delta******6Echo******7*******

Excel tables to the web >> Excel Jeanie HTML 4

I am in need of a macro that will scan a worksheet and insert columns and rows. I am working with information that is exported into excel. The problem that I am coming across is that if there is no information for a specific site or interval, the software that I am using excludes it from the exported excel file. What I need for this macro to do is insert a row with the missing interval. (for example, if interval 9:30 is missing, then an entire row should be inserted between 9:00 and 10:00 and then labeled 9:30 on column A. Intervals (rows) should range from 2:30 (row 2, cell A2) to 22:30 (row 42, cell A42). I need the same type of adjustment to be made for the columns. If there is no data for a specific Site, then a column should be inserted between the missing columns and a label should be created with the missing Site #. The column ranges are from (Col B, Cell B1 to Col E, Cell E1).

here is an example of what is exported.

Interval Site 1 Site 2 Site 3 Site 4 Total
7:00 22 13 35
7:30 57 6 47 110
8:00 32 46 59 137
8:30 22 3 85 79 189
9:00 76 43 140 83 342

How do you hide the column and row names?

I'm trying to copy a pivot table cell onto another worksheet that has a table with Month-Dates across the top in columns and left-most column has several cells, each with a named range. I get an application error with:

Code:
Sheets(shtSrc).rngSrc.Address.Copy _
     Sheets(shtDest).Range(Sheets(shtDest).Range(rngDest).Row, Dt.Column)
shtSrc and shtDest are Strings passed in to Sub for worksheet name.
rngSrc is a Range passed in to Sub.
rngDest is the Named Range of the destination cell.
All of the values get passed in; I'm just not using the range properly I think.
I'm not married to what I've got so far. Basically, as long as the pivot data gets copied at the intersection of the correct Date (Column) and Row I'll be happy.

Thanks!

How to prevent user resizing columns and rows in excel (2003) ?

Cheers

Ollie Riches

What cellformula, using column and row names, return a specific value from a
table like below? (I cant use the INDEX function since I dont know the column
or row numbers in the actual table, only the names)

A B C D F

1 Week 42 Week 43 Week 44 Week 45
2 Company 1 67% 71% 69% 81%
3 Company 2 45% 78% 79% 73%
4 Company 3 88% 67% 86% 74%
5 Company 4 97% 56% 77% 92%
6 Company 5 87% 55% 82% 69%

Number columns and rows in Excel, instead of having column lettering.
Example: C027R36 instead of AA36

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...ic.excel.setup

In my workbook all of the column and row headers on every spreadsheet have a
strikeout mark across the letter or number. I have no idea how this happened
and have not been able to change it back. Does anyone know how to fix this?
I am using Excel 2003
--
LizErnst

Hello, ive got difficulty how to show the column and row headers in
Excel worksheets, can anyone help me?

tnx!

--
graz
------------------------------------------------------------------------
graz's Profile: http://www.excelforum.com/member.php...o&userid=27611
View this thread: http://www.excelforum.com/showthread...hreadid=471335

I'm running Office 2007 and all my charts are showing up in reverse
order:

I want a bar graph that looks like this:

Series 1 -------------------------------->Column 1
Series 1 ------------------------->Column 2
Series 1 ------------------------------------->Column 3

Series 2 -------------------------------------->Column 1
Series 2------------------>Column 2
Series 2-------------------------->Column 3

What I'm getting is:

Series 2-------------------------->Column 3
Series 2------------------>Column 2
Series 2---------------------->Column 1

D3M ------------------------------------->Column 3
D3M ------------------------->Column 2
D3M -------------------------------->Column 1

All column and row headers are set to General, but I've tried setting
them to text as well. I need to have the columns in a certain order
for reporting.

I can only switch one column so the other still ends up out of order.
Ideally, I would just be able to click the button like Office 2007
says you're supposed to be able to and it would work.

Is there a setting somewhere that needs to be changed or has anyone
else experienced this problem?

Does anyone know how I can unhide columns and row headings. E.g. I can't see
the
headings

A B C D E F G
1
2
3
4
5