0


Hi Members,
please can anyone show me how I can write a blank line after writing each records? My intension is to space out the data in the spreadsheet for readability. So far the code I'm using is not doing it.

Seem my module below:

i = 5
 Do While Not rsin.EOF
    i = i + 1
    xlWksht.Cells(i, 1).Value = rsin![RequestID]
    xlWksht.Cells(i, 2).Value = rsin![Priority]
    xlWksht.Cells(i, 3).Value = rsin![Initials]
    xlWksht.Cells(i, 4).Value = rsin![Status]
    xlWksht.Cells(i, 5).Value = rsin![DReceivedDate]
    xlWksht.Cells(i, 6).Value = rsin![UpdateDate]
    xlWksht.Cells(i, 7).Value = rsin![Description]
    xlWksht.Cells(i + 1, 1).Value = Chr(10)  'Add a blank line after each record.
    rsin.MoveNext
 Loop

Thanks.
tgif

2
Contributors
9
Replies
10
Views
9 Years
Discussion Span
Last Post by binoj_daniel
0

Use this
xlWksht.Range(i + 1:1).Insert(Shift:=xlDown)

instead of xlWksht.Cells(i + 1, 1).Value = Chr(10) 'Add a blank line after each record.

Let me know.

0

Hi Binoj,
my module is not accepting the syntax: "xlWksht.Range(i + 1:1).Insert(Shift:=xlDown)". Hence, it's not working.
Thanks.
tgif

0

when I type in your code and try to compile, it beeps and hilights it in red.

It says: "Compile error". Expected: List separator or )", then it hilights the semicolon ":".

0

I changed the code to:

xlWksht.Range(i + 1, 1).Insert(Shift:=xlDown)

Then it says: "Compile error: expected ="

0

Here is another way of doing it. After you populate your excel file with all the records. Run this function.

Public Sub InsertRows()
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Row As Long

Set xl = New Excel.Application
Set wb = xl.Workbooks.Open("C:\test.xls")
Set ws = wb.Sheets("Sheet1")
ws.Select
With ActiveSheet
For Row = .UsedRange.Row + .UsedRange.Rows.Count - 1 To .UsedRange.Row Step -1
.Rows(Row).Offset(1).Insert
Next Row
End With
End Sub

0

Hi Binoj,
thank you for your insight and help. I achieved the objective by simply increasing the row variable by either 1 or two to skip a space or two before writing another line:

i = 6
xlwksht.cells(i + 2, 1).Value = ""

Thanks again.
tgifgemini.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.