Old Aussie needs Help with starting VBA

[thebigkangaroo]

Hi y’all.

I must first tell you that I and from Australia, 49 years old (and feel every year of it!).

I have very little programming experience but what I remember is that you have a list of commands/functions, when organised into a specific format (ie COBOL) and with the correct syntax, you have an action performed.

I have purchased the John Walkenbach Power Programming with VBA 1000 page book and I don’t know if I am coming or going. If I read the book 10 times front to back I may get somewhere but I would be dead from old age!! What I think I need is a list of commands/functions, in editable (WORD) format so I can use my own examples to understand what each command does etc and some rules about the structure and syntax of the application. Then you go about putting it all together to do little “things” until you learn enough to combine several “little things” to produce a useful program.

Questions for you modern guru’s:

1. Does anyone know where I can get an editable (prefer WORD) list of commands/functions for VBA?
2. Does anyone have any tips for an approach to take to learn VBA sufficiently to be able to develop useful programs for my own use. My brain unfortunately is not a sharp as it used to be

Thankyou very much in anticipation.

Kind Regards

Darren

 

[thebigkangaroo]

Deleting Blank Rows

Hi. I have a rather dumb question as I should be able to work this out myself. I am still very much a rookie. I just need a formula that will look down a table/list of data and delete the blank rows such that the above and below rows come together.

Thankyou all
Darren

 

[venkat1926]

thebigkangaroo

this is a trivial example try the experiment on this.
from A1 to C6
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <table class="MsoNormalTable" style="width:2.0in;margin-left:4.65pt;border-collapse:collapse;mso-padding-alt: 0in 5.4pt 0in 5.4pt" width="192" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:12.75pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap"> hdng1
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap"> hdng2
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap"> hdng3
</td> </tr> <tr style="mso-yfti-irow:1;height:12.75pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap"> a
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">

1​

</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">

2​

</td> </tr> <tr style="mso-yfti-irow:2;height:12.75pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">
</td> </tr> <tr style="mso-yfti-irow:3;height:12.75pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap"> s
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">

2​

</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">

3​

</td> </tr> <tr style="mso-yfti-irow:4;height:12.75pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">
</td> </tr> <tr style="mso-yfti-irow:5;mso-yfti-lastrow:yes;height:12.75pt"> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap"> d
</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">

3​

</td> <td style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:12.75pt" valign="bottom" width="64" nowrap="nowrap">

4​

</td> </tr> </tbody></table>
here row 3 and 5 are blank

just highlight the whole data and sort according to hdng1 /ascending (choose header row (that is header row available)

the blank rows will vanish.

 

[Michael M]

Hi Darren
Can't be done with a formula......
Venkats method and also a macro will do the trick.

This macro will delete any row that has a blank cell in column "A"

Sub delrow() 'DELETES ANY ROW IN COLUMN "A" THAT IS BLANK
    Dim i As Long
        For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
            If Range("A" & i).Value = "" Then Rows(i).Delete
        Next i
End Sub

 

[Peter_SSs]

I just need a formula that will look down a table/list of data and delete the blank rows such that the above and below rows come together.

Can't be done with a formula......

... unless you mean creating a new list like this Darren.

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B
1	Old List	New List
2	a	a
3	b	b
4		c
5	c	d
6		e
7		f
8	d
9	e
10
11	f
12
List without blanks

Another option may be to use AutoFilter to hide the blank rows.

 

[Michael M]

Hi Peter
My only concern with that method, would be adjacent data, if there is any, may be "corrupted"

look down a table/list of data and delete the blank rows such that the above and below rows come together.